1.自增主键
create table test( id int generated always as identity (cache 100 START WITH 1 INCREMENT BY 1) primary key , name varchar(100) )
CREATE TABLE GoodsStocksCL
(
GOODSORDERID int NOT NULL DEFAULT (1),
Ptypeid varchar (50) NOT NULL DEFAULT (''),
Ktypeid varchar (50) NOT NULL DEFAULT (''),
SLtypeid varchar (50) NOT NULL DEFAULT (''),
Qty numeric (22, 10) NOT NULL DEFAULT ((0)),
pgholInqty int NOT NULL DEFAULT ((0)),
CLtypeid varchar (50) NOT NULL DEFAULT (''),
CONSTRAINT PK_GoodsStocksCL PRIMARY KEY (GOODSORDERID, Ptypeid, Ktypeid, SLtypeid, CLtypeid) --主键
)
alter table表名 alter column id set default nextval(‘表名_id_seq’);
2.加备注
comment on table test is '测试'; --给表加注释 comment on column test.id is '序列';--给表字段加注释 comment on column test.name is '姓名';
查询备注
select c.relname 表名,cast(obj_description(relfilenode,'pg_class') as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum
3.默认值
CREATE TABLE IF NOT EXISTS public.pgdlydetail ( pgorder integer NOT NULL DEFAULT nextval('pgdlydetail_pgorder_seq'::regclass), vchcode integer NOT NULL DEFAULT 0, dlyorder integer NOT NULL DEFAULT 0, ptypeid character varying(50) COLLATE pg_catalog."default" NOT NULL DEFAULT '' )
4.唯一索引
CREATE INDEX index_name ON GoodsStocksCL(Ptypeid, Ktypeid,CLtypeid);--索引