postgres常用语句和操作

记录一下常用操作方便以后查询

I 安装

【引用】http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html

II 常用操作

添加column：

 ALTER TABLE officer ADD age integer default(0);

分步添加：

ALTER TABLE officer ADD age integer;

ALTER TABLE officer ALTER age SET DEFAULT 0;

删除column：

ALTER TABLE officer DROP age;

修改column类型:

ALTER TABLE exchange ALTER COLUMN price TYPE float4; （exchange表中price列的原来类型为integer）

添加联合主键：

ALTER TABLE ONLY exchange ADD CONSTRAINT exchange_pkey PRIMARY KEY (id, type);

添加外键：

ALTER TABLE account ADD CONSTRAINT fk_account_user FOREIGN KEY (user_name) REFERENCES user(user_name);

删除外键：

alter table 表名 drop constraint 外键约束名;

查询表结构：

\d table_name;

III 数据库备份/恢复

本地导出：

pg_dump -U yaoyu -f /home/yaoyu/dbback/data_bk_20170319_3.sql yaoyudb

远程导出：

pg_dump -h 192.168.1.122 -U yaoyu -d yaoyudb -F c -f /home/yaoyu/dbback/data_bk_20170323_t.dump (指定格式)

pg_dump -h 192.168.1.122 -U yaoyu -d yaoyudb -F p -f /home/yaoyu/dbback/data_bk_20170323_t.sql (sql明文)

仅导出表结构：

pg_dump -U yaoyu -d yaoyudb -s -f /home/yaoyu/dbback/data_bk_20170323_t.sql 

导入备份：

psql -U postgres -f /home/yaoyu/dbback/data_bk_20170323_t.sql yaoyudb > /home/dbback/postgres_date_import.log 2>&1

IV 其他

查看当前最大连接数：

show max_connections;

当前连接数：

SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

分用户查看当前连接数：

select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

用一个表的部分字段填充另一个表：

insert into t_user_account(user_name, bill_credit, bill_money, earn_money, update_date, total_money) 
select u.user_name, u.bill_credit, u.bill_money, u.earn_money, null, 0 fromuser u;