学习PostgreSQL

# 控制台连接数据库
psql -U user_name -d database_name -h host -p port

# 控制台常用命令
\h: 查看sql命令的解释,比如\h select
\?: 查看sql的命令列表
\l: 列出所有的数据库
\c [database_name]: 连接其他数据库
\d: 列出当前数据库的所有表格
\d [table_name]: 列出一张表格的结构
\du: 列出所有的用户
\e: 打开文本编辑器
\x: 扩展显示打开|关闭(以列的形式展示数据)
\o file_name: 将控制台输出重定向到文件file_name中
\conninfo: 列出当前的数据库和连接信息
\password user_name: 为用户设置密码
\q: 退出控制台

# 创建用户[设置密码]
CREATE USER user_name [WITH PASSWORD 'password'];

# 创建数据库并指定拥有者
CREATE DATABASE database_name OWNER user_name;

# 将数据库(database_name)的所有权都赋予用户(user_name),否则用户只能登陆,没有任何数据库操作权限
GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name

# 创建超级用户
CREATE USER user_name SUPERUSER PASSWORD 'password';

# 删除用户
DROP USER IF EXISTS user_name 

# 为用户指定查询路径(search_path)
ALTER USER user_name SET SEARCH_PATH TO schema_name, database_name;

# 更改数据库的owner
alter DATABASE database_name owner TO user_name

# 创建用户
sudo -u postgres CREATEuser --superuser user_name

# 设置密码
\password user_name

# shell命令下创建数据库并指定拥有者
sudo -u postgres CREATEdb -O user_name database_name

# 创建新表
CREATE TABLE table_name(name VARCHAR(20), time DATE);

# 插入数据
INSERT into table_name(name, time) VALUES('张三', '2017-08-02')

# 选择记录
select * from table_name;

# 删除记录
UPDATE table_name SET name = '李四' where name = '张三';

# 添加栏位
ALTER TABLE table_name ADD email VARCHAR(40);
ALTER TABLE table_name ADD id serial PRIMARY KEY;

# 更新结构     
ALTER TABLE table_name ALTER COLUMN time SET NOT NULL;

# 更名栏位
ALTER TABLE table_name RENANE COLUMN time TO currenttime;

# 表重命名
ALTER TABLE table_name RENAME TO new_name;

# 删除栏位
ALTER TABLE table_name DROP COLUMN email;

# 删除表格
DROP TABLE IF EXISTS table_name;

# 删除表格的内容 
TRUNCATE table_name;
DELETE FROM table_name;

# 转变column类型
SELECT * FROM table_name ORDER BY colunmn_str::integer;

# 增加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

# 删除主键
ALTER TABLE table_name DROP CONSTRAINT table_name_pkey;

# 获取数据库内schema下的所有表信息
SELECT * FROM INFORMATION_SCHEMA.TALBES WHERE TABLE_SCHEMA = <schema_name>

# 获取表的所有字段信息
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = <schema_name> AND TABLE_NAME = <table_name>

# 执行sql文件
psql -U user_name [-d] database_name < sql_file

# 备份文件
# pg_dump备份的时候，最好添加一个 --inserts参数，尽量备份出来dml语句格式的，因为是扩版本恢复，默认的可能存在问题
# --inserts参数备份下来的dump语句数据是insert into的方式一行行插入，而不是默认的copy形式，如果数据量大备份还原，建议不加这个参数
pg_dump --inserts -h hostname -p port -U user_name -d database_name [--schema=schema_name] -t table_name -f dump_file

# 生成从a到b的整数
FLOOR(random() * (b-a+1) + a)

# 生成从a到b的数
random() * (b-a+1) + a

# 保留m位小数
round(NUMERIC_VALUE, m)

# 从数组中任意生成一个
(array['0712', '0713', '0714', '0715', '0720', '0721'])[floor(random() * (6-1+1)) + 1]

# 把查询结果作为数组任意返回一个
(array(select colume from table))[floor(random() * (SIZE-1+1)) + 1]

# 从某个时间到某个时间随机日期
timestamp '2013-01-01 00:00:00' + random() * (timestamp '2019-11-01 00:00:00' - timestamp '2013-01-01 00:00:00')

#基于原有表中的数据造数据
select 'insert into ' || 'db_zxyw.t_zx_zxktc' || ' select replace(uuid_generate_v4()::text, ''-'', '''')' || ',' || (

select string_agg(col, ',') from (

    SELECT  column_name as col
      FROM information_schema.columns
     WHERE table_schema = 'db_zxyw'
       AND table_name   = 't_zx_zxktc'
         offset 1 rows
         
             
    ) a
)
		 
|| ' from ' || 'db_zxyw.t_zx_zxktc'

|| ' cross JOIN generate_series(1,10000) as a';

SELECT format(
  'ALTER TABLE %I.%I.%I OWNER TO %I;',
  table_catalog,
  table_schema,
  table_name,
  current_user  -- or another just put it in quotes
)
FROM information_schema.tables
WHERE table_schema = 'mySchema'

#查看指定schema下、指定owner下的所有表
select * from pg_tables where tableowner = 'gpadmin' and schemaname = 'xxx'

select * from pg_attribute where attribute = ${表名称}::regclass and attname = ${字段名称}
update pg_attribute set atttymod=${要修改的字段长度+4} where attrelid = ${表名称}::regclass and attname = ${字段名称}

grant select,insert,delete,update,truncate on all tables in schema ${schema_name} to ${user_name}
grant usage on schema ${schema_name} to ${user_name}

create user tyyw_rdonly ENCRYPTED PASSWORD '123456'
alter user tyyw_rdonly set default_transaction_read_only=on

--postgre高版本用这个（进到库里面执行）
grant select on all tables in schema tyyw to tyyw_rdonly 

--取消某个模式下表的授权
revoke select on all tables in schema $schema_name from $user

--授权某个表
grant select on tjrh.t0201 to $user;

--只有上面授权，可能还是提示schema没有权限
--执行schema的usage即可
GRANT USAGE ON SCHEMA $schema TO $user;

--postgre低版本
--生成grant语句
SELECT 'GRANT SELECT ON ' || table_schema || '.' table_name || ' to tyyw_rdonly;'
from information_schema.TABLES
where table_type = 'BASE_TABLE'
AND table_schema = ${schema_name}

--conf
select name,setting from pg_settings

--view
select pg_get_viewdef('viewname', true)

--index
select tablename,indexdef from pg_indexes where tablename ='${table_name}'

CREATE 
	OR REPLACE FUNCTION getalltables ( schemaname VARCHAR ) RETURNS VARCHAR AS $$ BEGIN
RETURN (SELEC
	string_agg ( table_name, ', ' ) 
FROM
	information_schema.TABLES 
WHERE
	table_schema = schemaname);
	END;
$$ LANGUAGE PLPGSQL;

SELECT
	'alter table tyyw.' || table_name || ' drop constraint ' || constraint_name || ';' AS my_query 
FROM
	information_schema.table_constraints 
WHERE
	table_schema = 'tyyw' 
	AND table_name in(select * from tyyw.tablenametest)
	AND constraint_type = 'PRIMARY KEY'

--step 1
REVOKE CONNECT ON DATABASE db_jcdsj FROM public;

--step 2 如果procid报不存在 应该是版本问题 用pid
SELECT 
   pg_terminate_backend(pg_stat_activity.procpid)
FROM 
   pg_stat_activity
WHERE 
   pg_stat_activity.datname = '${db_name}'
AND procpid <> pg_backend_pid();

-- 删除IDLE状态的数据库连接
select pg_terminate_backend(procpid) from pg_stat_activity where current_query='<IDLE>'