DDL synchronization from PostgreSQL to OceanBase Database in Oracle compatible mode

CREATE TABLE

• Create a table.

  The IF NOT EXISTS option is supported.

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

• Create an inline constraint.

  • 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));
  

• Create an out-of-line constraint.

  • 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));
  

• Create a generated column GENERATED ALWAYS AS ( generation_expr ) STORED.

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

• Create a named constraint CONSTRAINT constraint_name.

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

• Create a FOREIGN KEY option (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 a normal index.

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

• Create a unique index.

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

ALTER TABLE

• Modify the data type of a table field.

  ALTER TABLE T ALTER COLUMN C TYPE CHAR;
  

• Rename a table field.

  ALTER TABLE T RENAME COLUMN C TO C2; 
  

• Modify the default value of a field.

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

• Rename a table.

  ALTER TABLE T RENAME TO T2;
  

• Add a normal column.

  When you add a column, you can also define the NOT NULL/NULL/DEFAULT/CHECK constraints.

  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);
  

• Add a generated column.

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

• Drop a column.

  ALTER TABLE T DROP COLUMN C;
  

• Add a primary key.

  ALTER TABLE T ADD PRIMARY KEY(C);
  

  If the version of the OceanBase Database Oracle compatible mode is earlier than V4.0.0:

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

  If the version of the OceanBase Database Oracle compatible mode is V4.0.0 or later:

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

• Drop an index.

  ALTER TABLE t DROP CONSTRAINT cst
  

DROP TABLE

Drops a table.

DROP TABLE T1,T2;

TRUNCATE TABLE

Truncates a table.

TRUNCATE TABLE T1,T2;

COMMENT

Table comment.

COMMENT ON TABLE T IS 'TEST';