postgresql常用sql语句

一个比较好的学习postgres的站点

http://www.postgresqltutorial.com

显示当前连接数

select sum(numbackends) FROM pg_stat_database;
select datname,numbackends from pg_stat_database;
select * from pg_stat_activity;

--切换数据库
\c database_name;
--显示所有数据库
\l
--显示当前数据库的表
\dt
--显示user_info开头的表
\dt user_info*
--展示表结构
\d+ table_name;

重建主键

-- Firstly, remove PRIMARY KEY attribute of former PRIMARY KEY
ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkey;
-- Then change column name of  your PRIMARY KEY and PRIMARY KEY candidates properly.
ALTER TABLE <table_name> RENAME COLUMN <primary_key_candidate> TO id;
-- Lastly set your new PRIMARY KEY
ALTER TABLE <table_name> ADD PRIMARY KEY (id);

让主键自增

create sequence <table_name_primary_id_seq>;
--设定序列从1开始自增
select setval('<table_name_primary_id_seq>', 1,false);
--等同于以上语句
alter sequence <table_name_primary_id_seq> restart with 1;

alter table <table_name> alter <primary_id> set default nextval('<table_name_primary_id_seq>');

导出insert语句

pg_dump --column-inserts --data-only -d <database> -t <table>  > ./export.sql

计算百分比

select count(*) as item,(count(*)::decimal / (select count(*) from <table>)::decimal) * 100  as percent
from <table> where ....;

postgis应用

http://www.gonjay.com/blog/2015/05/15/postgis-cha-xun-fu-jin-de-ren/