PostgreSQL 数据库至 OceanBase 数据库 Oracle 兼容模式的同步 DDL

CREATE TABLE

• 创建表

  支持 IF NOT EXISTS 创建表

  CREATE TABLE T (C INT);
  CREATE TABLE IF NOT EXISTS T (C INT);
  

• 创建行内约束

  • NOT NULL

  • NULL

  • CHECK ( expression )

  • DEFAULT default_expr

  • UNIQUE

  • PRIMARY KEY

  • REFERENCES reftable(refcolumn)

  CREATE TABLE T (C INT NOT NULL);
  CREATE TABLE T (C INT NULL);
  CREATE TABLE T (C INT CHECK(C>0));
  CREATE TABLE T (C INT DEFAULT 1);
  CREATE TABLE T (C INT UNIQUE);
  CREATE TABLE T (C INT PRIMARY KEY);
  CREATE TABLE T (C INT REFERENCES P(PK));
  

• 创建行外约束

  • CHECK ( expression )

  • UNIQUE ( column_name [, ... ] )

  • PRIMARY KEY ( column_name [, ... ] )

  • FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable ( refcolumn [, ... ] )

  CREATE TABLE T(C INT,CHECK(C>0));
  CREATE TABLE T(C INT,UNIQUE (C));
  CREATE TABLE T(C INT, PRIMARY KEY(C));
  CREATE TABLE T(C INT, FOREIGN KEY(C) REFERENCES P(COL1));
  

• 创建生成列 GENERATED ALWAYS AS ( generation_expr ) STORED

  CREATE TABLE T(C1 INT, C2 INT GENERATED ALWAYS AS (C1+1) STORED);
  

• 约束支持命名约束 CONSTRAINT constraint_name

  CREATE TABLE T(C INT CONSTRAINT UK UNIQUE);
  CREATE TABLE T(C INT, CONSTRAINT FK1 FOREIGN KEY(C) REFERENCES P(COL1));
  

• FOREIGN 参数 (ON UPDATE | ON DELETE) (NO ACTION | RESTRICT | CASCADE)

  CREATE TABLE T(C INT, FOREIGN KEY(C) REFERENCES P(COL1) ON UPDATE NO ACTION);
  CREATE TABLE T(C INT, FOREIGN KEY(C) REFERENCES P(COL1) ON UPDATE RESTRICT);
  CREATE TABLE T(C INT, FOREIGN KEY(C) REFERENCES P(COL1) ON UPDATE CASCADE);
  CREATE TABLE T(C INT, FOREIGN KEY(C) REFERENCES P(COL1) ON DELETE NO ACTION);
  CREATE TABLE T(C INT, FOREIGN KEY(C) REFERENCES P(COL1) ON DELETE RESTRICT);
  CREATE TABLE T(C INT, FOREIGN KEY(C) REFERENCES P(COL1) ON DELETE CASCADE);
  

CREATE INDEX

• 创建普通索引

  CREATE INDEX ON T(C);
  CREATE INDEX idx1 ON T(C);
  

• 创建唯一索引

  CREATE UNIQUE INDEX ON T(C);
  CREATE UNIQUE INDEX unindex1 ON T(C);
  

ALTER TABLE

• 修改表字段类型

  ALTER TABLE T ALTER COLUMN C TYPE CHAR;
  

• 修改表字段名称

  ALTER TABLE T RENAME COLUMN C TO C2; 
  

• 修改字段 default 值

  ALTER TABLE T ALTER COLUMN C SET DEFAULT 1;
  ALTER TABLE T ALTER COLUMN C DROP DEFAULT;
  

• 修改表名

  ALTER TABLE T RENAME TO T2;
  

• 新增普通列

  新增列时支持同时定义 NOT NULL/NULL/DEFAULT/CHECK 约束

  ALTER TABLE t ADD COLUMN c INT NOT NULL DEFAULT 1;
  ALTER TABLE t ADD COLUMN c INT NULL;
  ALTER TABLE t ADD COLUMN c INT CONSTRAINT 'ck' CHECK(C>0);
  

• 新增生成列

  ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ( f+1 ) STORED;
  

• 删除列

  ALTER TABLE T DROP COLUMN C;
  

• 增加主键

  ALTER TABLE T ADD PRIMARY KEY(C);
  

  OceanBase 数据库 Oracle 兼容模式为 V4.0.0 之前版本时

  ALTER TABLE "T" ADD CONSTRAINT GENE_PRIMARY_CONSTRAINT UNIQUE ("C");
  

  OceanBase 数据库 Oracle 兼容模式为 V4.0.0 及之后版本时

  ALTER TABLE "t" ADD PRIMARY KEY ("c");
  

• 删除索引

  ALTER TABLE t DROP CONSTRAINT cst
  

DROP TABLE

删除表

DROP TABLE T1,T2;

TRUNCATE TABLE

清空表

TRUNCATE TABLE T1,T2;

COMMENT

表注释

COMMENT ON TABLE T IS 'TEST';