Synchronize DDL operations from a PostgreSQL database to OceanBase Database

CREATE TABLE

• Create a table

  You can use the IF NOT EXISTS clause to create a table.

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

• Specify the constraint name

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

• Specify the FOREIGN 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

• Change the data type of a column

  ALTER TABLE T ALTER COLUMN C TYPE CHAR;
  

• Change the name of a column

  ALTER TABLE T RENAME COLUMN C TO C2; 
  

• Change the default value of a column

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

• Change the name of a table

  ALTER TABLE T RENAME TO T2;
  

• Add a normal column

  You can specify the NOT NULL, NULL, DEFAULT, or CHECK constraint when you add a column.

  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 CHECK(C>0);
  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);
  

DROP TABLE

Drop a table

DROP TABLE T1,T2;

TRUNCATE TABLE

Truncate a table

TRUNCATE TABLE T1,T2;

COMMENT

Comment on a table

COMMENT ON TABLE T IS 'TEST';