postgreSQL常用命令

 

--建表案例
CREATE TABLE  gas_use_test  (
   id serial PRIMARY KEY NOT NULL,
   tenantId   integer NOT NULL,
   meterNo  varchar(255) unique NOT NULL  ,
   communicationDate  varchar(255) NOT NULL ,
   useGasAmount  decimal(10,4) DEFAULT NULL ,
   create_time  TIMESTAMP NOT null default now()
)

--批量更新
update test 
set info=tmp.info 
from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) 
where test.id=tmp.id;  

--不存在插入、存在更新ON CONFLICT 只在 PostgreSQL 9.5 以上可用。
insert into tablename (key1,key2,key3) values ('xxx','xxx','xxx') 
on conflict(key1) 
do update set key2 = 'yyy',key3 = 'yyy';


--修改表中的字段名（将key1修改为key2）
alter table tablename rename key1 to key2;
--表中新增字段
alter table tablename add key1 character varying not null;
--修改表名
alter table "tablename" rename to "new_tablename";
--删除表中某个字段
alter table tablename drop column if exists key1;


--表名、主键
SELECT * FROM pg_tables where tablename='gas_alarm'
select * from pg_catalog.pg_constraint 
select * from pg_catalog.pg_database pd 
select * from pg_catalog.pg_type pt 
--查看表结构
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 JOIN pg_description b
    ON a.attrelid = b.objoid
        AND a.attnum = b.objsubid, pg_type t
WHERE c.relname = 'ods_day_payrecord'
    AND a.attnum > 0
    AND a.attrelid = c.oid
    AND a.atttypid = t.oid
ORDER BY a.attnum;