工作中常用到的一些sql脚本

– 存储过程查询（存储过程/函数 关键词查询）
select * from pg_proc where lower(prosrc) like ‘%关键字%’;

– 复制表数据
insert into 表(字段) select 字段 from 表 where 条件;

– 查询重复数据
select COUNT(0),字段名 from 表名 where state = ‘E’ GROUP BY 字段名 HAVING COUNT(0) > 1;

– 表所属库
ALTER TABLE “用户名”.“表名”
OWNER TO “用户名”;

– 添加序列/创建序列
create sequence 序列名 start with 1000 INCREMENT by 1;

– 查询序列
select nextval(‘序列名’);

– 修改序列
–设置序列当前值
ALTER SEQUENCE 序列名 RESTART WITH 10000;

– 查询索引/查索引
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
idx.indisunique AS is_unique,
idx.indisprimary AS is_primary_key
FROM
pg_class t,
pg_class i,
pg_index idx,
pg_attribute a
WHERE
t.oid = idx.indrelid
AND i.oid = idx.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY(idx.indkey)
AND t.relkind = ‘r’
AND t.relname = ‘表名’;

SELECT *
FROM pg_indexes
WHERE tablename = ‘表名’;

– 创建索引
CREATE INDEX 索引名 ON 表名 (字段名);

– 创建联合索引
CREATE INDEX index_name ON table_name (column1, column2, …);

– 正在活跃的数据库会话
SELECT query_start,query,* FROM pg_stat_activity t where t.query != ‘’ and state = ‘active’;

– 杀死正在执行的数据库会话
SELECT pg_terminate_backend(‘68244’);

– 查询表空间
SELECT pg_size_pretty(pg_database_size(‘表名’)); – db_name

– 容量的最大的前10
select
tablename,
pg_size_pretty(size)
from
(select
tablename,
pg_total_relation_size(cast(tablename as text)) as size
from pg_tables
where schemaname = ‘用户名’ – 指定用户
) as tmp
order by size desc limit 10;

– 单张表的容量
SELECT pg_size_pretty(pg_total_relation_size(‘表名’));

– 给用户授权查询权限
GRANT SELECT ON 表名 TO 用户名;