1.对已有表设置自增
-- 创建自增序列
CREATE SEQUENCE sop_key_person_id_seq INCREMENT 1 START 1 MINVALUE 1;
COMMENT ON SEQUENCE sop_key_person_id_seq IS '主要人员表sop_key_person的id序列';
-- 把序列加在id字段上 ALTER TABLE tablename1 ALTER COLUMN "id" SET DEFAULT nextval('sequence_name1'::regclass);
ALTER TABLE "public"."sop_key_person" ALTER COLUMN "id"
SET DEFAULT nextval( 'sop_key_person_id_seq' :: regclass );
2.删除序列 DROP SEQUENCE sequence_name1, sequence_name2;
DROP SEQUENCE sop_alter_id_seq, sop_branch_id_seq;
3.查看库、表、索引存储空间
-- 查看数据库空间大小
select pg_size_pretty(pg_database_size('database1')) as size
-- 查看表空间大小(不包括索引)
select pg_size_pretty(pg_relation_size('tablename1'))
-- 查看表索引空间大小(语法同表空间)
select pg_size_pretty(pg_relation_size('tableindexname1')) as size
-- 查看表总大小(包括索引大小)
select pg_size_pretty(pg_total_relation_size('tablename1'))
-- 查询默认库/表空间大小
SELECT pg_size_pretty(pg_tablespace_size('pg_default')) as size
-- 查看所有库大小
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;
-- 查看指定schema 里所有的表大小，按从大到小的顺序排列(不包括索引)
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
-- 查看指定schema 里所有的表大小，按从大到小的顺序排列。(包括索引)
select relname, pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_total_relation_size(relid) desc
4.添加索引
-- 添加唯一索引 CREATE UNIQUE INDEX indexname1 ON tablename1(fieldname1, fieldname2)
CREATE UNIQUE INDEX sop_business_license_uk_pid on sop_business_license(pid);
-- 添加普通索引 CREATE INDEX indexname1 ON tablename1(fieldname1)
CREATE INDEX index_ent_name on sop_business_license(ent_name);
5.重命名索引 ALTER INDEX indexname1 RENAME TO indexname2
ALTER INDEX index_pid RENAME to sop_business_license_uk_pid;
6.查看索引
-- 查看表的索引列表 SELECT * FROM pg_index where tablename = 'tablename1'
select * from pg_indexes where tablename = 'sop_business_license'
-- 查看表的索引空间大小 select pg_size_pretty(pg_relation_size('indexname1')) as size
select pg_size_pretty(pg_relation_size('sop_business_license_pkey')) as size