PostgreSQL常用修改表结构

1、创建临时表

CREATE TABLE "test04" AS ( select * from testdemo);

2、修改表名

alter table table_name(表名) rename to new_table_name(新表名)

3、新增列并添加注释

alter table exchange_statistics add sys_id int4(32);
COMMENT ON COLUMN exchange_statistics.sys_id IS '系统id';

4、修改列属性

alter table exchange_statistics alter sys_id type varchar(255)；

5、删除列

ALTER TABLE test04 DROP COLUMN gid1_type;

6、修改列字段名

alter table test05 RENAME "gid" TO "id";

7、特殊的修改为integer

alter table table_name(表名)  alter column 字段名 type 新字段类型  using to_number(字段名,'9')

8、创建自增列

--增加自增序列
CREATE SEQUENCE user_wz_id_seq
    INCREMENT 1
    START 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 2;
--增加键id
alter table user_wz add column id int;
--修改键id为自增序列
alter table user_wz alter column id set default nextval('user_wz_id_seq');

9、查询表结构

SELECT a.attnum,
       a.attname AS field,
       t.typname AS type,
       a.attlen AS length,
       a.atttypmod AS lengthvar,
       a.attnotnull AS notnull,
       b.description AS comment
  FROM pg_class c,
       pg_attribute a
       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
       pg_type t
 WHERE c.relname = 'aoi_all'
       and a.attnum > 0
       and a.attrelid = c.oid
       and a.atttypid = t.oid
 ORDER BY a.attnum

10、修改自增序列的数据 从1开始计数

1）删除自增序列
	DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
2）重建序列
	CREATE SEQUENCE cof_table_hot_analysis_id_seq
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	CACHE 1; 
3)设置自增
	alter table cof_table_hot_analysis alter column id set default nextval('cof_table_hot_analysis _id_seq ');
	
4）清空表格时，需重置序列数据
	TRUNCATE TABLE cof_table_hot_analysis RESTART IDENTITY

11、创建函数

CREATE OR REPLACE FUNCTION concatkeywords(X TEXT, Y TEXT, namelist TEXT)
RETURNS TEXT AS $$
	SELECT CONCAT(a, b);
$$ 
LANGUAGE SQL;

12、修改密码

alter user background_prod with password ‘’;

13、死锁处理

SELECT * FROM pg_stat_activity WHERE datname=‘background_dev_test’ and client_addr =‘10.82.233.6’;
kill有两种方式，第一种是：
SELECT pg_cancel_backend(PID);
这种方式只能kill select查询，对update、delete 及DML不生效)

第二种是：
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作