Postgresql简单操作

create table public.table_name(
id                      bigserial        NOT NULL,             --自增id
col1                    varchar          NOT NULL DEFAULT ‘’,  --字段1
col2                    varchar          NOT NULL DEFAULT ‘’,  --字段2     
create_time             int8             NOT NULL DEFAULT 0,   --创建时间,时间戳
update_time             int8             NOT NULL DEFAULT 0,   --更新时间,时间戳
PRIMARY KEY (id)
);

COMMENT ON TABLE  public.table_name IS '演示表';

COMMENT ON COLUMN public.table_name.id                   IS '自增id';
COMMENT ON COLUMN public.table_name.col1                 IS '字段1';
COMMENT ON COLUMN public.table_name.col2                 IS '字段2';
COMMENT ON COLUMN public.table_name.create_time          IS '创建时间,时间戳';
COMMENT ON COLUMN public.table_name.update_time          IS '更新时间,时间戳';

alter table  public.table_name ALTER COLUMN col1 set DEFAULT  '1';

ALTER TABLE public.table_name ADD COLUMN col3 string  DEFAULT '';

ALTER TABLE public.table_name ALTER COLUMN col3 DROP DEFAULT;

ALTER TABLE public.table_name ADD column col3 int;

ALTER TABLE public.table_name  DROP COLUMN if exists col3 ;
ALTER TABLE public.table_name  DROP COLUMN col3 cascade;

alter table public.table_name alter column col1 type varchar USING col1 :: numeric(50,0);

alter table public.table_name alter column columnname type oldcolumntype USING columnname:: newcolumntype

alter table 表名 rename to 新表名;
alter table public.table_name rename to public.table_name_new;

alter table 表名 rename column 字段名 to 新字段名;
ALTER TABLE public.table_name RENAME col1 to col10 ;

ALTER TABLE public.table_name ADD PRIMARY KEY ("id");

ALTER TABLE public.table_name alter col1 set NOT NULL;

ALTER TABLE public.table_name MODIFY col1 varchar NOT NULL;

ALTER TABLE public.table_name alter col1 drop NOT NULL;

ALTER TABLE public.table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(col1, col2);

ALTER TABLE public.table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

ALTER TABLE public.table_name DROP CONSTRAINT MyUniqueConstraint;