CREATE TABLE

功能描述

在当前数据库中创建一个新的空白表，该表由命令执行者所有。

注意事项

• 本章节只包含shark新增的语法，原openGauss的语法未做删除和修改。
• 新增支持 AS expr [PERSISTED] 生成列语法
• 新增支持opt_clustered语法

语法格式

创建表。

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 } ]
    [ 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 |
    ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
    PRIMARY KEY index_parameters |
    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 ] |
    PRIMARY KEY [ opt_clustered ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters [ VISIBLE | INVISIBLE ] |
    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' ]
  

参数说明

• AS ( generation_expr ) [PERSISTED]

  该子句为兼容D库的语法，将字段创建为生成列，生成列的值在写入（插入或更新）数据时由generation_expr计算得到，PERSISTED表示像普通列一样存储生成列的值。

  说明：

  • PERSISTED关键字可省略，与不省略PERSISTED语义相同。
  • 兼容D库的生成列无需指定列类型，由表达式计算类型得到列的类型。
  • 兼容D库的生成列在删除生成列依赖的普通列时报错，必须先删除生成列，才能删除生成列依赖的普通列。

• opt_clustered

  参数内容为CLUSTERED/NONCLUSTERED，兼容D库的语法，指定创建聚合/非聚合索引。仅语法作用，没有实际功能。

生成列示例

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

相关链接

CREATE TABLE