altertable 表名 rename 现在的字段 to 新字段
altertable tb_test rename price to price_r
重置sequence
alter sequence 表名_id字段名_seq restart where 再次开始的id
alter sequence tb_test_id_seq restart with1;selectmax(id)from tb_test;--查出最大idselect nextval('tb_test_id_seq');--查出下个自增id值select setval('tb_test_id_seq',50);--设置需求id值
NOT NULL
增加 notnull 约束
altertable tbl_null alterCOLUMN b setnotnull
移除notnull约束
altertable tbl_null alterCOLUMN b dropnotnull
select array_to_string(array(select id from table_name),', ');
获取指定字段去重的列表
selectdistincton(t.id , t,name)*fromtable t
查看表数据类型等
SELECT
a.attname as 字段名,
format_type(a.atttypid,a.atttypmod)as 类型,
a.attnotnull as 非空, col_description(a.attrelid,a.attnum)as 注释
FROM
pg_class as c,pg_attribute as a
where
a.attrelid = c.oid
and
a.attnum>0and
c.relname ='table_name';
in 、not in中包含null
select column_name from table_name where column_name in(1,null);-- in条件中包含null也不会返回该字段为null的数据,自动过滤select column_name from table_name where column_name notin(1,null);-- not in条件中包含null会直接返回false,啥都不会返回