postgresql 常用sql

• 切换数据库
  \c databaseName

• 查看数据库、表、索引
  postgres=# \l 数据库； \dt 表； \di 索引；

• 查看库、表大小

select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; 
或
 select pg_size_pretty(pg_database_size('databaseName'));
或
select pg_size_pretty(pg_tablespace_size('tableName')); 

• 修改自增索引起始
  select setval('code_configs_id_seq',10,false);

• 数组类型分列
  unnest(anyarray)
jsonb_array_elements('[{a:1},{a:2}]')

• 截图字符串
  select split_part(substring('/api/index?AA&channel=00&fefefe' from '%#"channel%&?#"%' for '#'),'&',1);

• 替换
  update temp set name = replace(name,'_','')

• 删除约束
  ALTER TABLE table_name DROP CONSTRAINT some_name;

• 查看权限
  select * from information_schema.table_privileges where grantee='user_name';

• 时间
  to_date(to_char(orderd + interval '8 hours','YYYYMMDD'),'YYYYMMDD')