postgresql 实用sql

清理重复数据

• 利用ctid删除重复数据

delete from <tablename> where ctid not in (select min(ctid) from <tablename> group by id)

查看空间占用

-- 查看索引占用空间大小
select pg_size_pretty(pg_relation_size('playboy_id_pk'));

-- 查看数据占用空间大小
select pg_database_size('playboy');
-- 查看所有数据库的大小
select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;  
-- 以KB，MB，GB的方式来查看数据库大小
select pg_size_pretty(pg_database_size('playboy')); 

-- 查看表大小
select pg_relation_size('test');   
#以KB，MB，GB的方式来查看表大小
select pg_size_pretty(pg_relation_size('test'));   
-- 查看表的总大小，包括索引大小
select pg_size_pretty(pg_total_relation_size('test'));   

-- 查看表大小
select pg_relation_size('test');
-- 以KB，MB，GB的方式来查看表大小
select pg_size_pretty(pg_relation_size('test'));   
-- 查看表的总大小，包括索引大小
select pg_size_pretty(pg_total_relation_size('test'));   

-- 看所有表空间
select spcname from pg_tablespace;  
-- 查看表空间大小
select pg_size_pretty(pg_tablespace_size('pg_default')); 

提取视图中用的表

select * from ( select unnest ( REGEXP_MATCHES ( regexp_replace ( <VIEW_SQL>, '\s', ' ', 'g' ),'(?:(?:[fF][Rr][Oo][Mm])|(?:[Jj][Oo][Ii][Nn]))\s*([\w\d]*\.(?:[\w\d]*))\s*[\w\d]*\s*(?:(?:,(\s*([\w\d]*\.[\w\d]*))\s*[\w\d]*\s*)*)', 'g' ) )tablename )T where tablename is not null

查询正在执行的申请了

select * from pg_stat_activity where state <> 'idle'

-- 强制结束查询
SELECT pg_terminate_backend(PID);

查看数据库连接数

-- 当前连接数
select count(*) from pg_stat_activity;
select count(*), usename,client_addr  from pg_stat_activity group by usename,client_addr order by count(*) desc;
-- 最大连接数
show max_connections;

聚合 - GROUPING SETS

-- 带总计
select usename,client_addr,count(*)  from pg_stat_activity group by GROUPING SETS((usename,client_addr),())  order by count(*) asc;

-- 自由分组
select usename,client_addr,count(*)  from pg_stat_activity group by GROUPING SETS((usename,client_addr),(usename),());

权限控制

• 数据库权限

-- 赋权(database权限)
alter database <database_name> owner to <user_name>;

-- 收回此需删除的用户对数据库的所有权限
revoke all on database <database_name> from <user_name>;

• 模式权限

-- 将schema 赋权
grant usage,create on schema <schema_name> to <user_name>;

-- 将schema 赋权
revoke usage,create on schema <schema_name> from <user_name>;

• 表权限

-- 将schema_name模式下的所有表的select、update权限赋予user_name用户
grant [{insert|update|insert|delete}|{insert,update....}] on all tables in schema <schema_name> to <user_name>;

-- 将schema_name模式下的所有表的select、update权限从user_name用户下收回
revoke [{insert|update|insert|delete}|{insert,update....}] on all tables in schema <schema_name> from <user_name>;

• 复制权限

-- 将user1用户的权限赋予user2
grant <user1> to <user2>;

-- 可以解决一个用户建的表，对另个一用户授权。
-- 将rolename1的对schemaname的所有表的所有权限赋予rolename2
alter default privileges for role <rolename1> in schema <schema_name> grant all on tables to <rolename2>

-- 将所有用户对schemaname的所有表的所有权限赋予rolename2
alter default privileges in schema <schema_name> grant all on tables to <rolename2>