--建表案例 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;
标签:postgreSQL,--,key1,tablename,pg,常用命令,NULL,alter From: https://www.cnblogs.com/wangbin2188/p/16987864.html