postgre中修改表结构的语句

1.新建表(自增长序列)

DROP TABLE IF EXISTS ptb.pd_patient;
CREATE TABLE ptb.pd_patient (
   person_id             BIGSERIAL PRIMARY KEY,
   organization_id       INT NOT NULL,
   patient_id            VARCHAR(255) NOT NULL,
   created_date          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT pd_patient_pk PRIMARY KEY (person_id)
);
ALTER SEQUENCE ptb.pd_patient_person_id_seq RESTART WITH 30000000020;

--BIGSERIAL ,SERIAL默认生产的序列:表_字段_seq;

select nextval('ptb.pd_patient_person_id_seq');
select currval('ptb.pd_patient_person_id_seq');

-- 手动给 postgre 增加自增长系列;

CREATE SEQUENCE ptb.tb_discussion_serial START 1 OWNED BY ptb.tb_discussion.discussion_id;

ALTER TABLE ptb.tb_discussion ALTER COLUMN discussion_id SET DEFAULT nextval('ptb.tb_discussion_serial');

 

为已有的字段添加默认值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

删除默认值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

删除一个字段

ALTER TABLE ptb.tb_patient_tracking DROP COLUMN report_id;

新增一个字段

ALTER TABLE ptb.tb_patient_tracking add report_id int4;

字段类型varchar 转int

alter table  ptb.gtr_gene_Test  alter genomic_report_id  type Int4 USING genomic_report_id::int4 ;