Suggestions on Synchronizing Data to GaussDB(DWS)

Table and column names contain backquotes.

Replace backquotes with double quotation marks.

create table `t1`( `c1`  int, `c2`  varchar(10) );

CREATE TABLE "public"."t1"(  "c1" INTEGER,  "c2" VARCHAR(40))  WITH ( ORIENTATION = ROW, COMPRESSION = NO )  NOCOMPRESS  DISTRIBUTE BY HASH ("c1");

N/A

Key field name

Create indexes.

Create Table mall_order_dc (id bigint NOT NULL AUTO_INCREMENT,order_id varchar(50) NOT NULL,key order_id(id));

CREATE TABLE "public"."mall_order_dc"(
  "id" BIGSERIAL NOT NULL,
  "order_id" VARCHAR(200) NOT NULL)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");
CREATE INDEX "order_id" ON "public"."mall_order_dc" USING BTREE ("id");

N/A

Custom field names are the same as the names of hidden fields.

Add the suffix _new.

Create Table mall_order_dc (
xc_node_id bigint NOT NULL AUTO_INCREMENT,
tableoid varchar(50) NOT NULL,
cmax int,
xmax int,
cmin char,
xmin varchar(10),
ctid smallint,
tid time,
tidd int,
ctidd int
);

CREATE TABLE "public"."mall_order_dc"(
  "xc_node_id_new" BIGSERIAL NOT NULL,
  "tableoid_new" VARCHAR(200) NOT NULL,
  "cmax_new" INTEGER,
  "xmax_new" INTEGER,
  "cmin_new" CHAR(4),
  "xmin_new" VARCHAR(40),
  "ctid_new" SMALLINT,
  "tid_new" TIME WITHOUT TIME ZONE,
  "tidd" INTEGER,
  "ctidd" INTEGER)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("xc_node_id_new");

GaussDB(DWS) has the following system fields: xc_node_id, tableoid, cmax, xmax, cmin, xmin, ctid, and tid. They will be suffixed with _new.

GaussDB(DWS) keywords are used as field names.

Add double quotation marks to the field names.

N/A

N/A

If any table or field using keywords like desc, checksum, operator, and size as its name, the table and field names will be enclosed in double quotation marks.

GaussDB(DWS) keywords are used as table names.

Add double quotation marks to the table names.

N/A

N/A

If any table or field using keywords like user as its name, the table and field names will be enclosed in double quotation marks.

RENAME TABLE

N/A

RENAME TABLE department TO newdept;
RENAME TABLE employee TO pepole;
RENAME TABLE newdept TO newdept_02,pepole to pepole_02;

ALTER TABLE "public"."department" RENAME TO "newdept";
ALTER TABLE "public"."employee" RENAME TO "pepole";
ALTER TABLE "public"."newdept" RENAME TO "newdept_02";
ALTER TABLE "public"."pepole" RENAME TO "pepole_02";

A parallel rename statement is converted into multiple rename statements.

SET DEFAULT

N/A

ALTER TABLE runoob_alter_test ALTER dataType2 SET DEFAULT 1;
ALTER TABLE runoob_alter_test ALTER COLUMN dataType2 SET DEFAULT 3;

ALTER TABLE runoob_alter_test ALTER dataType2 SET DEFAULT '1';
ALTER TABLE runoob_alter_test ALTER COLUMN dataType2 SET DEFAULT '3';

ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" SET DEFAULT '1';
ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" SET DEFAULT '3';

ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" SET DEFAULT '1';
ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" SET DEFAULT '3';

N/A

CHANGE

N/A

ALTER TABLE runoob_alter_test CHANGE dataType1 dataType1New VARCHAR(50);
ALTER TABLE runoob_alter_test CHANGE dataType2 dataType2New VARCHAR(50) NOT NULL;
ALTER TABLE runoob_alter_test CHANGE dataType3 dataType3New VARCHAR(100) FIRST;
ALTER TABLE runoob_alter_test CHANGE dataType4 dataType4New VARCHAR(50) AFTER dataType1;

ALTER TABLE "public"."runoob_alter_test" CHANGE COLUMN "datatype1" "datatype1new" VARCHAR(200);
ALTER TABLE "public"."runoob_alter_test" CHANGE COLUMN "datatype2" "datatype2new" VARCHAR(200) NOT NULL;
ALTER TABLE "public"."runoob_alter_test" CHANGE COLUMN "datatype3" "datatype3new" VARCHAR(400);
ALTER TABLE "public"."runoob_alter_test" CHANGE COLUMN "datatype4" "datatype4new" VARCHAR(200);

N/A

MODIFY

N/A

ALTER TABLE runoob_alter_test modify datatype1 char default 1;
ALTER TABLE runoob_alter_test modify datatype1 char default '1';
ALTER TABLE runoob_alter_test modify datatype1 char default "1";

ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" CHAR(4) DEFAULT '1';
ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" CHAR(4) DEFAULT '1';
ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" CHAR(4) DEFAULT '1';

N/A

ADD PRIMARY KEY

N/A

ALTER TABLE runoob_alter_test ADD PRIMARY KEY (dataType1);

ALTER TABLE "public"."runoob_alter_test" ADD PRIMARY KEY("datatype1");

N/A

DROP PRIMARY KEY

N/A

ALTER TABLE runoob_alter_test DROP PRIMARY KEY;

ALTER TABLE "public"."runoob_alter_test" DROP CONSTRAINT IF EXISTS runoob_alter_test_pkey;

N/A

ADD COLUMN

N/A

ALTER TABLE runoob_alter_test ADD dataType1_1 INT NOT NULL AFTER dataType1;

ALTER TABLE "public"."runoob_alter_test" ADD COLUMN "datatype1_1" INTEGER NOT NULL;

N/A

ADD COLUMN

N/A

ALTER TABLE runoob_alter_test ADD dataType1_1 INT NOT NULL AFTER dataType1;

ALTER TABLE "public"."runoob_alter_test" ADD COLUMN "datatype1_1" INTEGER NOT NULL;

N/A

ADD INDEX

N/A

ALTER TABLE runoob_tbl ADD INDEX idex_runoob_id(runoob_id) USING BTREE;

CREATE INDEX "idex_runoob_id" ON "public"."runoob_tbl" ("runoob_id");

N/A

ADD UNIQUE INDEX

Generate a common index using the unique key.

ALTER TABLE runoob_tbl ADD UNIQUE KEY IDEX_runoob_id USING BTREE (runoob_id);

CREATE INDEX "idex_runoob_id" ON "public"."runoob_tbl" ("runoob_id");

N/A

DROP INDEX

N/A

ALTER TABLE runoob_tbl DROP KEY IDEX_runoob_id;

DROP INDEX "public"."idex_runoob_id" RESTRICT;

N/A

ALGORITHM

Delete

ALTER TABLE runoob_alter_test ALGORITHM=DEFAULT;
ALTER TABLE runoob_alter_test ALGORITHM=INPLACE;
ALTER TABLE runoob_alter_test ALGORITHM=COPY;

N/A

N/A

DEFAULT CHARACTER SET

Delete

ALTER TABLE runoob_alter_test CHARACTER SET=utf8;
ALTER TABLE runoob_alter_test DEFAULT CHARACTER SET=utf8;

N/A

N/A

COLLATE

Delete

N/A

N/A

COLLATE specifies a default database sorting rule.

DELAY_KEY_WRITE

Delete

CREATE TABLE `public`.`runoob_tbl_test`(
 `runoob_id` VARCHAR(30),
 `runoob_title` VARCHAR(100) NOT NULL,
 `runoob_author` VARCHAR(40) NOT NULL,
 `submission_date` VARCHAR(30)
) ENGINE=MyISAM, DELAY_KEY_WRITE=0;
ALTER TABLE `public`.`runoob_tbl_test6` DELAY_KEY_WRITE=1;

CREATE TABLE "PUBLIC"."RUNOOB_TBL_TEST"(
 "RUNOOB_ID" VARCHAR(30),
 "RUNOOB_TITLE" VARCHAR(100) NOT NULL,
 "RUNOOB_AUTHOR" VARCHAR(40) NOT NULL,
 "SUBMISSION_DATE" VARCHAR(30)
) WITH ( ORIENTATION = ROW, COMPRESSION = NO )
 NOCOMPRESS
 DISTRIBUTE BY HASH ("RUNOOB_ID");

DELAY_KEY_WRITE specifies how to use delayed key writes and is valid only for MyISAM tables. If this field is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the table is closed.

DIRECTORY

Delete

CREATE TABLE `public`.`runoob_tbl_test1` (
`dataType1` int NOT NULL AUTO_INCREMENT,
`dataType2` DOUBLE(20,8), 
PRIMARY KEY(`dataType1`)
) ENGINE=MYISAM DATA DIRECTORY = 'D:\\input' INDEX DIRECTORY= 'D:\\input';
CREATE TABLE `public`.`runoob_tbl_test2` (
`dataType1` int NOT NULL AUTO_INCREMENT, 
`dataType2` DOUBLE(20,8), 
PRIMARY KEY(`dataType1`)
) ENGINE=INNODB DATA DIRECTORY = 'D:\\input'

CREATE TABLE "public"."runoob_tbl_test1"(
 "datatype1" SERIAL NOT NULL,
 "datatype2" FLOAT(20),
 PRIMARY KEY ("datatype1")
) WITH ( ORIENTATION = ROW, COMPRESSION = NO )
 NOCOMPRESS
 DISTRIBUTE BY HASH ("datatype1");
CREATE TABLE "public"."runoob_tbl_test2"(
 "datatype1" SERIAL NOT NULL,
 "datatype2" FLOAT(20),
 PRIMARY KEY ("datatype1")
) WITH ( ORIENTATION = ROW, COMPRESSION = NO )
 NOCOMPRESS
 DISTRIBUTE BY HASH ("datatype1");

N/A

INSERT_METHOD

Delete

CREATE TABLE `public`.`runoob_alter_test`(
 `dataType1` int NOT NULL AUTO_INCREMENT,
 `dataType2` DOUBLE(20,8),
 `dataType3` TEXT NOT NULL,
 PRIMARY KEY(`dataType1`)
) INSERT_METHOD=LAST;
ALTER TABLE runoob_alter_test INSERT_METHOD NO;
ALTER TABLE runoob_alter_test INSERT_METHOD=NO;
ALTER TABLE runoob_alter_test INSERT_METHOD FIRST;
ALTER TABLE runoob_alter_test INSERT_METHOD=FIRST;
ALTER TABLE runoob_alter_test INSERT_METHOD LAST;
ALTER TABLE runoob_alter_test INSERT_METHOD=LAST;

CREATE TABLE "public"."runoob_alter_test"(
 "datatype1" SERIAL NOT NULL,
 "datatype2" FLOAT(20),
 "datatype3" TEXT NOT NULL,
 PRIMARY KEY ("datatype1")
) WITH ( ORIENTATION = ROW, COMPRESSION = NO )
 NOCOMPRESS
 DISTRIBUTE BY HASH ("datatype1");

N/A

KEY_BLOCK_SIZE

Delete

CREATE TABLE `public`.`runoob_tbl_test`(
 `runoob_id` VARCHAR(30),
 `runoob_title` VARCHAR(100) NOT NULL,
 `runoob_author` VARCHAR(40) NOT NULL,
 `submission_date` VARCHAR(30)
) ENGINE=MyISAM KEY_BLOCK_SIZE=8;
ALTER TABLE runoob_tbl_test ENGINE=InnoDB;
ALTER TABLE runoob_tbl_test KEY_BLOCK_SIZE=0;

CREATE TABLE "public"."runoob_tbl_test"(
 "runoob_id" VARCHAR(30),
 "runoob_title" VARCHAR(100) NOT NULL,
 "runoob_author" VARCHAR(40) NOT NULL,
 "submission_date" VARCHAR(30)
) WITH ( ORIENTATION = ROW, COMPRESSION = NO )
 NOCOMPRESS
 DISTRIBUTE BY HASH ("runoob_id");

N/A

MAX_ROWS

Delete

CREATE TABLE `public`.`runoob_alter_test`(
 `dataType1` int NOT NULL AUTO_INCREMENT,
 `dataType2` DOUBLE(20,8),
 `dataType3` TEXT NOT NULL,
 PRIMARY KEY(`dataType1`)
);
ALTER TABLE runoob_alter_test MAX_ROWS 100000;
ALTER TABLE runoob_alter_test MAX_ROWS=100000;

CREATE TABLE "public"."runoob_alter_test"(
"datatype1" SERIAL NOT NULL,
"datatype2" FLOAT(20),
"datatype3" TEXT NOT NULL,
PRIMARY KEY ("datatype1")
) WITH ( ORIENTATION = ROW, COMPRESSION = NO )
NOCOMPRESS
DISTRIBUTE BY HASH ("datatype1");

N/A

MIN_ROWS

Delete

CREATE TABLE `public`.`runoob_alter_test`(
`dataType1` int NOT NULL AUTO_INCREMENT,
`dataType2` DOUBLE(20,8),
`dataType3` TEXT NOT NULL,
PRIMARY KEY(`dataType1`)
);
ALTER TABLE runoob_alter_test MIN_ROWS 10000;
ALTER TABLE runoob_alter_test MIN_ROWS=10000;

CREATE TABLE "public"."runoob_alter_test"(
"datatype1" SERIAL NOT NULL,
"datatype2" FLOAT(20),
"datatype3" TEXT NOT NULL,
PRIMARY KEY ("datatype1")
)WITH ( ORIENTATION = ROW, COMPRESSION = NO )
NOCOMPRESS
DISTRIBUTE BY HASH ("datatype1");

N/A

PACK_KEYS

Delete

CREATE TABLE `public`.`runoob_alter_test`(
`dataType1` int NOT NULL AUTO_INCREMENT,
`dataType2` DOUBLE(20,8),
`dataType3` TEXT NOT NULL,
PRIMARY KEY(`dataType1`)
) ENGINE=MyISAM PACK_KEYS=1;
##A
ALTER TABLE runoob_alter_test PACK_KEYS 0;
ALTER TABLE runoob_alter_test PACK_KEYS=0;
##B
ALTER TABLE runoob_alter_test PACK_KEYS 1;
ALTER TABLE runoob_alter_test PACK_KEYS=1;
##C
ALTER TABLE runoob_alter_test PACK_KEYS DEFAULT;
ALTER TABLE runoob_alter_test PACK_KEYS=DEFAULT;

CREATE TABLE "public"."runoob_alter_test"(
"datatype1" SERIAL NOT NULL,
"datatype2" FLOAT(10),
"datatype3" FLOAT(20),
"datatype4" TEXT NOT NULL,
PRIMARY KEY ("datatype1")
)WITH ( ORIENTATION = ROW, COMPRESSION = NO )
NOCOMPRESS
DISTRIBUTE BY HASH ("datatype1");
--A
--B
--C

N/A

PASSWORD

Delete

CREATE TABLE `public`.`runoob_alter_test`(

`dataType1` int NOT NULL AUTO_INCREMENT,

`dataType2` DOUBLE(20,8),

`dataType3` TEXT NOT NULL,

PRIMARY KEY(`dataType1`)

);

ALTER TABLE runoob_alter_test PASSWORD 'HELLO';

CREATE TABLE "public"."runoob_alter_test"

(

"datatype1" SERIAL NOT NULL,

"datatype2" FLOAT(20),

"datatype3" TEXT NOT NULL,

PRIMARY KEY ("datatype1")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("datatype1");

N/A

STATS_AUTO_RECALC

Delete

CREATE TABLE `public`.`runoob_alter_test`(

`runoob_id` VARCHAR(30),

`runoob_title` VARCHAR(100) NOT NULL,

`runoob_author` VARCHAR(40) NOT NULL,

`submission_date` VARCHAR(30)

) ENGINE=InnoDB, STATS_AUTO_RECALC=DEFAULT;

## A.

ALTER TABLE runoob_alter_test STATS_AUTO_RECALC DEFAULT;

ALTER TABLE runoob_alter_test STATS_AUTO_RECALC=DEFAULT;

## B.

ALTER TABLE runoob_alter_test STATS_AUTO_RECALC 0;

ALTER TABLE runoob_alter_test STATS_AUTO_RECALC=0;

## C.

ALTER TABLE runoob_alter_test STATS_AUTO_RECALC 1;

ALTER TABLE runoob_alter_test STATS_AUTO_RECALC=1;

CREATE TABLE "public"."runoob_alter_test"

(

"runoob_id" VARCHAR(30),

"runoob_title" VARCHAR(100) NOT NULL,

"runoob_author" VARCHAR(40) NOT NULL,

"submission_date" VARCHAR(30)

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("runoob_id");

-- A.

-- B.

-- C

N/A

STATS_PERSISTENT

Delete

CREATE TABLE `public`.`runoob_alter_test`(

`dataType1` int NOT NULL AUTO_INCREMENT,

`dataType2` DOUBLE(20,8),

`dataType3` TEXT NOT NULL,

PRIMARY KEY(`dataType1`)

) ENGINE=InnoDB, STATS_PERSISTENT=0;

## A.

ALTER TABLE runoob_alter_test STATS_PERSISTENT DEFAULT;

ALTER TABLE runoob_alter_test STATS_PERSISTENT=DEFAULT;

## B.

ALTER TABLE runoob_alter_test STATS_PERSISTENT 0;

ALTER TABLE runoob_alter_test STATS_PERSISTENT=0;

## C.

ALTER TABLE runoob_alter_test STATS_PERSISTENT 1;

ALTER TABLE runoob_alter_test STATS_PERSISTENT=1

CREATE TABLE "public"."runoob_alter_test"

(

"datatype1" SERIAL NOT NULL,

"datatype2" FLOAT(20),

"datatype3" TEXT NOT NULL,

PRIMARY KEY ("datatype1")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("datatype1");

-- A.

-- B.

-- C.

N/A

STATS_SAMPLE_PAGES

Delete

CREATE TABLE `public`.`runoob_alter_test`(

`dataType1` int NOT NULL AUTO_INCREMENT,

`dataType2` DOUBLE(20,8),

`dataType3` TEXT NOT NULL,

PRIMARY KEY(`dataType1`)

) ENGINE=InnoDB,STATS_SAMPLE_PAGES=25;

ALTER TABLE runoob_alter_test STATS_SAMPLE_PAGES 100;

ALTER TABLE runoob_alter_test STATS_SAMPLE_PAGES=100;

CREATE TABLE "public"."runoob_alter_test"

(

"datatype1" SERIAL NOT NULL,

"datatype2" FLOAT(20),

"datatype3" TEXT NOT NULL,

PRIMARY KEY ("datatype1")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("datatype1");

N/A

The option ENGINE is specified when you create a table.

Delete the option ENGINE.

CREATE TABLE `public`.`runoob_alter_test`(

`dataType1` int NOT NULL,

`dataType2` DOUBLE(20,8),

PRIMARY KEY(`dataType1`)

)ENGINE=MYISAM;

## A.

ALTER TABLE runoob_alter_test ENGINE INNODB;

ALTER TABLE runoob_alter_test ENGINE=INNODB;

## B.

ALTER TABLE runoob_alter_test ENGINE MYISAM;

ALTER TABLE runoob_alter_test ENGINE=MYISAM;

## C.

ALTER TABLE runoob_alter_test ENGINE MEMORY;

ALTER TABLE runoob_alter_test ENGINE=MEMORY

CREATE TABLE "public"."runoob_alter_test"

(

"datatype1" INTEGER NOT NULL,

"datatype2" FLOAT(20),

PRIMARY KEY ("datatype1")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("datatype1");

-- A.

-- B.

-- C.

ENGINE specifies the storage engine for a MySQL table. During synchronization, ENGINE is deleted if it is set to ARCHIVE, BLACKHOLE, CSV, FEDERATED, INNODB, MYISAM, MEMORY, MRG_MYISAM, NDB, NDBCLUSTER, or PERFOMANCE_SCHEMA.

CHECKSUM

Delete

CREATE TABLE `public`.`runoob_alter_test`(

`dataType1` int NOT NULL AUTO_INCREMENT,

`dataType2` FLOAT(10,2),

`dataType3` DOUBLE(20,8),

PRIMARY KEY(`dataType1`)

) CHECKSUM=1;

ALTER TABLE runoob_alter_test CHECKSUM 0;

ALTER TABLE runoob_alter_test CHECKSUM=0;

ALTER TABLE runoob_alter_test CHECKSUM 1;

ALTER TABLE runoob_alter_test CHECKSUM=1

CREATE TABLE "public"."runoob_alter_test"

(

"datatype1" SERIAL NOT NULL,

"datatype2" REAL,

"datatype3" DOUBLE PRECISION,

PRIMARY KEY ("datatype1")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("datatype1");

N/A

ON UPDATE CURRENT_TIMESTAMP

Delete

drop table if exists unsupport_parse_test;

create table `unsupport_parse_test` (

`username` int,

`update` timestamp not null default current_timestamp on update current_timestamp

);

DROP TABLE IF EXISTS "public"."unsupport_parse_test";

CREATE TABLE "public"."unsupport_parse_test"

(

"username" INTEGER,

"update" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("username");

N/A

SET FOREIGN_KEY_CHECKS=0 or 1

Delete

N/A

N/A

N/A

PRIMARY KEY (`id`) USING BTREE

Delete

Create Table mall_order_dc (

id int,

name varchar(10),

primary key(`id`) using btree

);

CREATE TABLE "public"."mall_order_dc"

(

"id" INTEGER,

"name" VARCHAR(40),

PRIMARY KEY ("id")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("id");

The default value is btree. This keyword is not required.

Row-column storage conversion

Create tables using row-store by default.

N/A

N/A

N/A

create table if not exists

The keyword if not exists is supported.

N/A

N/A

N/A

charset

Delete this keyword during synchronization.

N/A

N/A

CHARSET specifies the default character set for a table.

Partition table

Range partitioning is supported.

drop table if exists `runoob_tbl_part_test`;

CREATE TABLE IF NOT EXISTS `runoob_tbl_part_test`(

`runoob_id` INT NOT NULL,

`runoob_title` VARCHAR(100) NOT NULL,

`runoob_author` VARCHAR(40) NOT NULL,

`submission_date` INT

)ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE COLUMNS(runoob_id, submission_date)(

PARTITION p0 VALUES LESS THAN(123, MAXVALUE),

PARTITION p1 VALUES LESS THAN(200, MAXVALUE),

PARTITION p2 VALUES LESS THAN(300, MAXVALUE),

PARTITION p3 VALUES LESS THAN(400, MAXVALUE),

PARTITION p4 VALUES LESS THAN(500, MAXVALUE),

PARTITION p5 VALUES LESS THAN(MAXVALUE, MAXVALUE)

);

CREATE TABLE IF NOT EXISTS "public"."runoob_tbl_part_test"

(

"runoob_id" INTEGER NOT NULL,

"runoob_title" VARCHAR(400) NOT NULL,

"runoob_author" VARCHAR(160) NOT NULL,

"submission_date" INTEGER

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("runoob_id")

PARTITION BY RANGE ("runoob_id", "submission_date")

(

PARTITION p0 VALUES LESS THAN (123, MAXVALUE),

PARTITION p1 VALUES LESS THAN (200, MAXVALUE),

PARTITION p2 VALUES LESS THAN (300, MAXVALUE),

PARTITION p3 VALUES LESS THAN (400, MAXVALUE),

PARTITION p4 VALUES LESS THAN (500, MAXVALUE),

PARTITION p5 VALUES LESS THAN (MAXVALUE, MAXVALUE)

);

N/A

Character encoding specified by a field

Delete

CREATE TABLE `FCT_TRADE_***_RT` (

`KID` INT(11) NOT NULL AUTO_INCREMENT,

`YM` VARCHAR(7) CHARSET UTF8MB4 COLLATE UTF8MB4_BIN NOT NULL COMMENT 'YYYY-MM, for example, 2019-04'

);

CREATE TABLE "public"."fct_trade_***_rt"

(

"kid" SERIAL NOT NULL,

"ym" VARCHAR(28) NOT NULL COMMENT 'YYYY-MM, for example, 2019-04'

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("kid");

N/A

Integer with data width

Delete the data width.

CREATE TABLE `FCT_TRADE_***_RT` (

`KID` INT(11) NOT NULL AUTO_INCREMENT,

`YM` SMALLINT(15),

`c3` BIGINT(50)

);

CREATE TABLE "public"."fct_trade_***_rt"

(

"kid" SERIAL NOT NULL,

"ym" SMALLINT,

"c3" BIGINT

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("kid");

The number types int, smallint, and bigint do not support data width and will be deleted.

unsigned zerofill

Delete

CREATE TABLE IF NOT EXISTS `runoob_dataType_test`(

`dataType_1` DEC,

`dataType_2` DEC(10),

`dataType_3` DEC(10, 2) UNSIGNED ZEROFILL,

`dataType_4` DEC(10, 2) ZEROFILL

)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"

(

"datatype_1" DECIMAL,

"datatype_2" DECIMAL(10),

"datatype_3" DECIMAL(10,2),

"datatype_4" DECIMAL(10,2)

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("datatype_1");

N/A

auto_increment

This attribute is not supported. Convert this attribute to SERIAL and delete it during synchronization.

CREATE TABLE `public`.`job_instance` (

`job_sche_id` int(11) NOT NULL AUTO_INCREMENT,

`task_name` varchar(100) NOT NULL DEFAULT '',

PRIMARY KEY (`job_sche_id`)

) ENGINE=InnoDB AUTO_INCREMENT=219 DEFAULT CHARSET=utf8

CREATE TABLE "public"."job_instance"

(

"job_sche_id" SERIAL NOT NULL,

"task_name" VARCHAR(100) NOT NULL DEFAULT '',

PRIMARY KEY ("job_sche_id")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("job_sche_id");

N/A

AVG_ROW_LENGTH

This attribute is not supported. Convert this attribute to SERIAL and delete it during synchronization.

CREATE TABLE `public`.`runoob_tbl_test`(

`runoob_id` VARCHAR(30),

`runoob_title` VARCHAR(100) NOT NULL,

`runoob_author` VARCHAR(40) NOT NULL,

`submission_date` VARCHAR(30)

)AVG_ROW_LENGTH=10000;

CREATE TABLE "public"."runoob_tbl_test"

(

"runoob_id" VARCHAR(30),

"runoob_title" VARCHAR(100) NOT NULL,

"runoob_author" VARCHAR(40) NOT NULL,

"submission_date" VARCHAR(30)

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("runoob_id")

N/A

UNION

Convert this function to the CREATE VIEW statement in GaussDB during synchronization.

CREATE TABLE t1 (

a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

message CHAR(20)

) ENGINE=MyISAM;

CREATE TABLE t2 (

a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

message CHAR(20)

) ENGINE=MyISAM;

CREATE TABLE total (

a INT NOT NULL AUTO_INCREMENT,

message CHAR(20))

ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

CREATE TABLE t1 (

a SERIAL NOT NULL PRIMARY KEY,

message CHAR(20)

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("a");

CREATE TABLE t2 (

a SERIAL NOT NULL PRIMARY KEY,

message CHAR(20)

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("a");

CREATE VIEW total(a, message) AS

SELECT * FROM t1

UNION ALL

SELECT * FROM t2;

UNION is an option used in table creation and it works only when you create MERGE tables. Creating a table using this option is similar to creating a common view. The created table logically combines the data of multiple tables that are specified by UNION.

LIKE (table cloning)

Add additional table attribute information.

CREATE TABLE IF NOT EXISTS `public`.`runoob_tbl_old`(

`dataType_1` YEAR,

`dataType_2` YEAR(4),

`dataType_3` YEAR DEFAULT '2018',

`dataType_4` TIME DEFAULT NULL

);

CREATE TABLE `runoob_tbl` (like `runoob_tbl_old`);

CREATE TABLE "public"."runoob_tbl_old"

(

"datatype_1" VARCHAR(4),

"datatype_2" VARCHAR(4),

"datatype_3" VARCHAR(4) DEFAULT '2018',

"datatype_4" TIME WITHOUT TIME ZONE DEFAULT NULL

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("datatype_1");

CREATE TABLE "public"."runoob_tbl"( LIKE "public"."runoob_tbl_old"

INCLUDING COMMENTS INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING INDEXES

INCLUDING STORAGE);

N/A

DROP TABLE

The default schema is public.

DROP TABLE `test_create_table01`;

DROP TABLE "public"."test_create_table01";

N/A

TRUNCATE (table deletion)

N/A

TRUNCATE TABLE `test_create_table01`;

TRUNCATE TABLE "public"."test_create_table01" CONTINUE IDENTITY RESTRICT;

N/A

Hash indexes

Replace these indexes with common indexes based on GaussDB features during synchronization.

CREATE TABLE `public`.`test_create_table03` (

`DEMAND_ID` INT(11) NOT NULL AUTO_INCREMENT,

`DEMAND_NAME` CHAR(100) NOT NULL,

`THEME` VARCHAR(200) NULL DEFAULT NULL,

`SEND_ID` INT(11) NULL DEFAULT NULL,

`SEND_NAME` CHAR(20) NULL DEFAULT NULL,

`SEND_TIME` DATETIME NULL DEFAULT NULL,

`DEMAND_CONTENT` TEXT NOT NULL,

PRIMARY KEY(`DEMAND_ID`),

INDEX CON_INDEX(DEMAND_CONTENT(100)) USING HASH ,

INDEX SEND_INFO_INDEX USING HASH (SEND_ID,SEND_NAME(10),SEND_TIME)

);

ALTER TABLE runoob_alter_test ADD KEY alterTable_addKey_indexType(dataType1) USING HASH;

CREATE TABLE "public"."test_create_table03"

(

"demand_id" SERIAL NOT NULL,

"demand_name" CHAR(100) NOT NULL,

"theme" VARCHAR(200) DEFAULT NULL,

"send_id" INTEGER(11) DEFAULT NULL,

"send_name" CHAR(20) DEFAULT NULL,

"send_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,

"demand_content" TEXT NOT NULL,

PRIMARY KEY ("demand_id")

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("demand_id");

CREATE INDEX "con_index" ON "public"."test_create_table03" ("demand_content");

CREATE INDEX "send_info_index" ON "public"."test_create_table03"

("send_id","send_name","send_time");

CREATE INDEX "altertable_addkey_indextype" ON "public"."runoob_alter_test" ("datatype1");

N/A

B-tree indexes

DRS automatically adjusts the DDL statements based on GaussDB features during synchronization.

ALTER TABLE runoob_alter_test ADD KEY alterTable_addKey_indexType (dataType1) USING BTREE;

CREATE INDEX "altertable_addkey_indextype" ON "public"."runoob_alter_test" ("datatype1");

N/A

Spatial indexes

DRS automatically adjusts the DDL statements based on GaussDB features during synchronization.

CREATE TABLE `public`.`test_create_table04` (

`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`A` POINT NOT NULL,

`B` POLYGON NOT NULL,

`C` GEOMETRYCOLLECTION NOT NULL,

`D` LINESTRING NOT NULL,

`E` MULTILINESTRING NOT NULL,

`F` MULTIPOINT NOT NULL,

`G` MULTIPOLYGON NOT NULL,

SPATIAL INDEX A_INDEX(A),

SPATIAL INDEX B_INDEX(B),

SPATIAL INDEX C_INDEX(C),

SPATIAL KEY D_INDEX(D),

SPATIAL KEY E_INDEX(E),

SPATIAL KEY F_INDEX(F),

SPATIAL INDEX G_INDEX(G)

);

CREATE TABLE "public"."test_create_table04"

(

"id" SERIAL NOT NULL PRIMARY KEY,

"a" POINT NOT NULL,

"b" POLYGON NOT NULL,

"c" CIRCLE NOT NULL,

"d" POLYGON NOT NULL,

"e" BOX NOT NULL,

"f" BOX NOT NULL,

"g" POLYGON NOT NULL

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("id");

CREATE INDEX "a_index" ON "public"."test_create_table04" USING GIST ("a");

CREATE INDEX "b_index" ON "public"."test_create_table04" USING GIST ("b");

CREATE INDEX "c_index" ON "public"."test_create_table04" USING GIST ("c");

CREATE INDEX "d_index" ON "public"."test_create_table04" USING GIST ("d");

CREATE INDEX "e_index" ON "public"."test_create_table04" USING GIST ("e");

CREATE INDEX "f_index" ON "public"."test_create_table04" USING GIST ("f");

CREATE INDEX "g_index" ON "public"."test_create_table04" USING GIST ("g")

N/A

Full-text indexes

DRS automatically adjusts the DDL statements based on GaussDB features during synchronization.

CREATE TABLE `public`.`test_create_table02` (

`ID` INT(11) NOT NULL PRIMARY KEY,

`TITLE` CHAR(255) NOT NULL,

`CONTENT` TEXT NULL,

`CREATE_TIME` DATETIME NULL DEFAULT NULL,

FULLTEXT (`CONTENT`)

);

CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`

(

`id` INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(128) NOT NULL,

FULLTEXT INDEX (name)

);

CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`

(

`id` INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(128) NOT NULL,

FULLTEXT INDEX (name ASC)

);

CREATE TABLE "public"."test_create_table02"

(

"id" INTEGER(11) NOT NULL PRIMARY KEY,

"title" CHAR(255) NOT NULL,

"content" TEXT,

"create_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("id");

CREATE INDEX "idx_test_create_table02_content" ON "public"."test_create_table02" USING

GIN(to_tsvector(coalesce("content",'')));

CREATE TABLE "public"."runoob_datatype_test"

(

"id" SERIAL PRIMARY KEY,

"name" VARCHAR(128) NOT NULL

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("id");

CREATE INDEX "idx_runoob_datatype_test_name" ON "public"."runoob_datatype_test" USING

GIN(to_tsvector(coalesce("name",'')));

CREATE TABLE "public"."runoob_datatype_test"

(

"id" SERIAL PRIMARY KEY,

"name" VARCHAR(128) NOT NULL

)

WITH ( ORIENTATION = ROW, COMPRESSION = NO )

NOCOMPRESS

DISTRIBUTE BY HASH ("id");

CREATE INDEX "idx_runoob_datatype_test_name" ON "public"."runoob_datatype_test" USING

GIN(to_tsvector(coalesce("name",'')));

N/A

CHECK constraint

DRS automatically adjusts the DDL statements based on GaussDB features during synchronization.

drop table if exists `t1`;

create table if not exists t1 (

id int(25) not null primary key check (id > 1 and id < 100),

city varchar(255) not null unique check (city='CityA' or city='CityB' or city='CityC'),

population int(25) not null ,

constraint t1_1 check (population>0 and population<10000)

) ;

DROP TABLE IF EXISTS "public"."t1";

CREATE TABLE IF NOT EXISTS "public"."t1" (

"id" INTEGER NOT NULL PRIMARY KEY CHECK (

id > 1

and id < 100

),

"city" VARCHAR(1020) NOT NULL CHECK (

city = 'CityA'

or city = 'CityB'

or city = 'CityC'

),

"population" INTEGER NOT NULL,

CONSTRAINT t1_1 CHECK (

population > 0

and population < 10000

)

) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("id

-

GENERATED ALWAYS AS

Delete

create table `createTable_others11` (

id INT PRIMARY KEY,

price DECIMAL(10, 2),

discount DECIMAL(4, 2),

discounted_price DECIMAL(10, 2) GENERATED ALWAYS AS (price - price * discount) STORED

);

-

-