1、序列
以自增serial类型主键的序列:
alter sequence s_seq restart with 1; #重置序列
select currval('tablename_pid_seq');
select nextval('tablename_pid_seq');
select setval('tablename_pid_seq',1);
2、修改表
--删除约束
alter table tablename alter column col drop not null;
--添加约束
alter table tablename add constraint tablename_col_uq unique(col);
--修改类型/长度
alter table tablename alter column col type character varying(2000);
--添加列
alter table tablename add column col character varying(255);
--删除列
alter table tablename drop column col;
3、查看数据使用空间
select pg_size_pretty(pg_database_size('dbname'));
4、优化与监控
#查看数据库
select * from pg_stat_database
#查看链接情况
select * from pg_stat_activity where datname = 't0pagped'
select count(1) from pg_stat_activity;
#根据IP地址汇总连接数
select client_addr,count(client_addr) as num from pg_stat_activity
where datname = 't0pagped' and application_name = 'PostgreSQL JDBC Driver'
group by client_addr
order by num desc
#最大连接数
show max_connections;
#查看表使用情况
select * from pg_stat_user_tables where relname = 'kg_askbob_entity_ins_capital' order by seq_scan desc
#查看表索引
select * from pg_stat_user_indexes where relname = 'kg_askbob_entity_ins_capital'
PostgreSQL统计信息:
https://yq.aliyun.com/articles/697692