Postgresql表操作大全

---

一、查询当前用户下包含的表

SELECT relname as table_name
  FROM pg_class 
 WHERE 1 = 1 
   AND relkind = 'r' 
   AND relowner = ( SELECT usesysid FROM pg_user WHERE usename = (select current_user) )
   AND relname not like 'com_%'
   AND relname NOT IN ('tzq_log_events_t','ddl_audit_log')
 order by relname;

二、修改表名

alter table tzq_test_t rename to tzq_test_t_old;

三、表增加字段

ALTER TABLE tzq_test_t ADD COLUMN log_name VARCHAR(200);

或者加上IF NOT EXISTS判断：

ALTER TABLE tzq_test_t ADD COLUMN IF NOT EXISTS log_name VARCHAR(200);

四、修改字段名

ALTER TABLE tzq_test_t RENAME COLUMN log_name TO log_name_new;

五、修改表字段类型

ALTER TABLE tzq_test_t ALTER COLUMN log_id TYPE varchar(200);
ALTER TABLE tzq_test_t ALTER COLUMN status TYPE int8 USING status::int8;

六、删除字段默认值

ALTER TABLE tzq_test_t ALTER COLUMN status DROP DEFAULT;

七、字段加注释

COMMENT ON COLUMN tzq_test_t.status IS '状态:0-失效，1-生效';

八、修改序列名

alter sequence seq_tzq_test_t rename to seq_tzq_test_t_new;

九、约束

9.1、删除主键约束

ALTER TABLE tzq_test_t DROP CONSTRAINT tzq_test_t_pkey;

9.2、创建主键约束

ALTER TABLE tzq_test_t ADD CONSTRAINT tzq_test_t_pkey primary key (log_id, log_name);

十、修改索引名字

ALTER INDEX idx_tzq_test_t_1 RENAME TO idx_tzq_test_t_1_new;

十一、PG给某个字段增加/删除非空约束

11.1、删除非空约束

alter table tzq_test_t alter log_id drop not null;

11.2、添加非空约束

alter table tzq_test_t alter column log_id set not null;

十二、数值字段置空

update tzq_log_t 
   set log_name = null
 where log_id = 48;

十三、pg波浪号查询

select * from PG_TIMEZONE_NAMES where name ~* 'Etc/GMT-8';

十四、pg查询表不用加schema

alter user asst set search_path to "$user", fnd, public;