CREATE TABLE
功能描述
在当前数据库中创建一个新的空白表，该表由命令执行者所有。
注意事项
- 本章节只包含shark新增的语法，原openGauss的语法未做删除和修改。
- 新增支持
AS expr [PERSISTED]生成列语法。 - 新增支持
opt_clustered语法。 - 建表语句中，针对UNIQUE和PRIMARY KEY约束，支持通过WITH给出选项，对应index_parameters子句，新增支持的选项包括：
FILLFACTOR = fillfactor
| PAD_INDEX = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| XML_COMPRESSION = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ MINUTES | MINUTE ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
其中FILLFACTOR选项的取值fillfactor为[1, 100]的整数，实际含义同A库（A库的取值范围为[10, 100]的整数），因此当D库中fillfactor的取值范围为[1, 10)，不报错，将打印notice信息，并将fillfactor的取值设置为A库的最小值10; COMPRESSION_DELAY选项的取值delay为[0, 10080]的整数; 除FILLFACTOR选项含有实际功能，同A库，其余参数均无实际功能，仅语法支持。
- 建表语句中，针对UNIQUE和PRIMARY KEY约束，支持ON {filegroup | “default” } 选项，无实际作用，仅语法支持。
- 建表语句新增支持ON {filegroup | “default” } 选项，无实际作用，仅语法支持。
- 建表语句新增支持TEXTIMAGE_ON { filegroup | “default” } 选项，无实际作用，仅语法支持。
- filegroup为任意字符串，支持通过[]包裹。
- 如果同时指定ON filegroup子句和TEXTIMAGE_ON filegroup子句，ON filegroup子句应位于前面，否则会出现语法报错。
- ON/TEXTIMAGE_ON filegroup子句无法和ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }子句同时存在。
语法格式
创建表。
CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ CHARACTER SET | CHARSET charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option [...] ] }
[, ... ])
[ AUTO_INCREMENT [ = ] value ]
[ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ] [ [DEFAULT] COLLATE [ = ] default_collation ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] | [ ON filegroup ] | [ TEXTIMAGE_ON filegroup ] ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
[ COMMENT {=| } 'text' ];
其中列约束column_constraint为：
[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | GENERATED ALWAYS AS ( generation_expr ) [STORED] | AS ( generation_expr ) [PERSISTED] | AUTO_INCREMENT | ON UPDATE update_expr | UNIQUE [KEY] index_parameters [ ON filegroup ] | ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) | PRIMARY KEY index_parameters [ ON filegroup ] | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ ENABLE [VALIDATE | NOVALIDATE] | DISABLE [VALIDATE | NOVALIDATE] ] [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ COMMENT {=| } 'text' ]其中表约束table_constraint为：
[ CONSTRAINT [ constraint_name ] ] { CHECK ( expression ) | UNIQUE [ opt_clustered ] ( { { column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters [ VISIBLE | INVISIBLE ] [ ON filegroup ] | PRIMARY KEY [ opt_clustered ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters [ VISIBLE | INVISIBLE ] [ ON filegroup ] | FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ (refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] | PARTIAL CLUSTER KEY ( column_name [, ... ] ) } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ COMMENT {=| } 'text' ]其中索引参数index_parameters为：
[ WITH ( {storage_parameter = value} [, ... ] ) ] [ USING INDEX TABLESPACE tablespace_name ]
参数说明
AS ( generation_expr ) [PERSISTED]
该子句为兼容D库的语法，将字段创建为生成列，生成列的值在写入（插入或更新）数据时由generation_expr计算得到，PERSISTED表示像普通列一样存储生成列的值。
说明：- PERSISTED关键字可省略，与不省略PERSISTED语义相同。
- 兼容D库的生成列无需指定列类型，由表达式计算类型得到列的类型。
- 兼容D库的生成列在删除生成列依赖的普通列时报错，必须先删除生成列，才能删除生成列依赖的普通列。
opt_clustered
参数内容为CLUSTERED/NONCLUSTERED，兼容D库的语法，指定创建聚合/非聚合索引。仅语法作用，没有实际功能。
WITH ( { storage_parameter = value } [, … ] )
这个子句为表或索引指定一个可选的存储参数。用于表的WITH子句还可以包含OIDS=FALSE表示不分配OID。
针对UNIQUE和PRIMARY KEY约束，新增支持的storage_parameter选项包括：
FILLFACTOR
int类型，填充因子，实际的含义和功能同A库。
取值范围：[1, 100]的整数，A库的取值范围为[10, 100]的整数，因此当D库中fillfactor的取值范围为[1, 10)，不报错，将打印notice信息，并将fillfactor的取值设置为A库的最小值10。
PAD_INDEX
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
IGNORE_DUP_KEY
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
STATISTICS_NORECOMPUTE
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
STATISTICS_INCREMENTAL
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
ALLOW_ROW_LOCKS
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
ALLOW_PAGE_LOCKS
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
OPTIMIZE_FOR_SEQUENTIAL_KEY
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
XML_COMPRESSION
bool类型，无实际功能，仅语法兼容。
取值范围：ON或者OFF。
COMPRESSION_DELAY
int类型，单位MINUTES或者MINUTE，可选，无实际功能，仅语法兼容。
取值范围：0 | delay [ MINUTES | MINUTE ]，其中delay为[0, 10080]的整数。
DATA_COMPRESSION
string类型，无实际功能，仅语法兼容。
取值范围：NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE。
filegroup
- 建表语句中，针对UNIQUE和PRIMARY KEY约束，支持ON {filegroup | “default” } 选项，无实际作用，仅语法支持。
- 建表语句新增支持ON {filegroup | “default” } 选项，无实际作用，仅语法支持。
- 建表语句新增支持TEXTIMAGE_ON { filegroup | “default” } 选项，无实际作用，仅语法支持。
- filegroup为任意字符串，支持通过[]包裹。
- 如果同时指定ON filegroup子句和TEXTIMAGE_ON filegroup子句，ON filegroup子句应位于前面，否则会出现语法报错。
- ON/TEXTIMAGE_ON filegroup子句无法和ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }子句同时存在。
生成列示例
opengauss=# CREATE TABLE Products(
opengauss(# QtyAvailable smallint,
opengauss(# UnitPrice money,
opengauss(# InventoryValue AS (QtyAvailable * UnitPrice)
opengauss(# );
NOTICE: The virtual computed columns (non-persisted) are currently ignored and behave the same as persisted columns.
CREATE TABLE
opengauss=# ALTER TABLE Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5) PERSISTED;
ALTER TABLE
opengauss=# \d+ Products
Table "public.products"
Column | Type | Modifiers | Storage | Stats target | Description
----------------+----------+-----------------------------------------------------------------------+---------+--------------+-------------
qtyavailable | smallint | | plain | |
unitprice | money | | plain | |
inventoryvalue | money | as ((qtyavailable * unitprice)) persisted | plain | |
retailvalue | money | as (((qtyavailable * unitprice) * (1.5)::double precision)) persisted | plain | |
Has OIDs: no
Options: orientation=row, compression=no
opengauss=# ALTER TABLE Products DROP unitprice;
ERROR: cannot drop a column used by a generated column
DETAIL: Column "unitprice" is used by generated column "retailvalue".
opengauss=# ALTER TABLE Products DROP inventoryvalue;
ALTER TABLE
opengauss=# ALTER TABLE Products DROP retailvalue;
ALTER TABLE
opengauss=# ALTER TABLE Products DROP unitprice;
ALTER TABLE
WITH ( { storage_parameter = value } [, … ] )示例
create table test_with_1(a int, CONSTRAINT PK_test_with_1 PRIMARY KEY(a)
WITH (PAD_INDEX = OFF, FILLFACTOR = 50, IGNORE_DUP_KEY = off, STATISTICS_NORECOMPUTE = off, STATISTICS_INCREMENTAL = off,
ALLOW_ROW_LOCKS = off, ALLOW_PAGE_LOCKS = off, OPTIMIZE_FOR_SEQUENTIAL_KEY = off, XML_COMPRESSION = off));
NOTICE: parameter "pad_index" is currently ignored.
NOTICE: parameter "ignore_dup_key" is currently ignored.
NOTICE: parameter "statistics_norecompute" is currently ignored.
NOTICE: parameter "statistics_incremental" is currently ignored.
NOTICE: parameter "allow_row_locks" is currently ignored.
NOTICE: parameter "allow_page_locks" is currently ignored.
NOTICE: parameter "optimize_for_sequential_key" is currently ignored.
NOTICE: parameter "xml_compression" is currently ignored.
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_test_with_1" for table "test_with_1"
create table test_with_2(a int, CONSTRAINT PK_test_with_2 PRIMARY KEY(a) with (COMPRESSION_DELAY = 0 MINUTES));
NOTICE: parameter "compression_delay" is currently ignored.
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_test_with_2" for table "test_with_2"
create table test_with_3(a int, CONSTRAINT PK_test_with_3 PRIMARY KEY(a) with (COMPRESSION_DELAY = 10080 minute));
NOTICE: parameter "compression_delay" is currently ignored.
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_test_with_3" for table "test_with_3"
create table test_with_4(a int, CONSTRAINT PK_test_with_4 PRIMARY KEY(a) with (data_compression = COLUMNSTORE_ARCHIVE));
NOTICE: parameter "data_compression" is currently ignored.
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_test_with_4" for table "test_with_4"
create table test_with_5(a int, PRIMARY KEY(a) with (pad_index = on, fillfactor = 20));
NOTICE: parameter "pad_index" is currently ignored.
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_with_5_pkey" for table "test_with_5"
create table test_with_6(a int, PRIMARY KEY(a) with (pad_index = on, fillfactor = 1));
NOTICE: parameter "pad_index" is currently ignored.
NOTICE: parameter fillfactor will be set to 10 when it is less than 10.
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_with_6_pkey" for table "test_with_6"
create table test_with_7(a int, UNIQUE(a) with (pad_index = on, fillfactor = 1));
NOTICE: parameter "pad_index" is currently ignored.
NOTICE: parameter fillfactor will be set to 10 when it is less than 10.
NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_with_7_a_key" for table "test_with_7"
filegroup示例
create table t1(a int) on [primary];
create table t2(a int) on "default";
create table t3(id int) on [filegroup];
create table t4(id int) on filegroup;
create table t5(id int) on 'filegroup';
create table t6(id int) on "filegroup";
create table t7(a int) textimage_on [primary];
create table t8(a int) textimage_on "default";
create table t9(a int) on "default" textimage_on [primary];
create table t10(a int) on "default" textimage_on "default";
create table t11(a int PRIMARY KEY WITH (PAD_INDEX = OFF) ON [primary]) ON [primary];
create table t12(a int UNIQUE WITH (XML_COMPRESSION = OFF) ON [primary]) ON [primary];
create table t13(a int, CONSTRAINT PK_t11 PRIMARY KEY(a) WITH (PAD_INDEX = OFF) ON [primary]) ON [primary];
create table t14(a int, CONSTRAINT PK_t12 UNIQUE(a) WITH (XML_COMPRESSION = OFF) ON [primary]) ON [primary];
相关链接
意见反馈