云数据库 GAUSSDB-CREATE TABLE:语法格式

时间：2023-12-14 09:05:30

云数据库 GAUSSDB SQL语法

语法格式

创建表。

CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
        | table_constraint
        | LIKE source_table [ like_option [...] ] }
        [, ... ])
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
    [ COMPRESS | NOCOMPRESS ]
    [ TABLESPACE tablespace_name ]
    [ DISTRIBUTE BY { REPLICATION | HASH ( column_name [, ...] ) 
    | RANGE ( column_name [, ...] ) { SLICE REFERENCES tablename | ( slice_less_than_item [, ...] )
    | ( slice_start_end_item [, ...] ) }
    | LIST ( column_name [, ...] ) { SLICE REFERENCES tablename | ( slice_values_item [, ...] ) }
    } ]
    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

其中列约束column_constraint为：

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) | 
  DEFAULT default_expr |
  UNIQUE [ index_parameters ] |
  PRIMARY KEY [ index_parameters ] |
  ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) }
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

其中列的压缩可选项compress_mode为：

1	{ DELTA \| PREFIX \| DICTIONARY \| NUMSTR \| NOCOMPRESS }

其中表约束table_constraint为：

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE ( column_name [, ... ] ) [ index_parameters ] |
  PRIMARY KEY ( column_name [, ... ] ) [ index_parameters ] |
  PARTIAL CLUSTER KEY ( column_name [, ... ] ) }
[ DEFERRABLE | NOT DEFERRABLE ][ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

其中like选项like_option为：

1	{ INCLUDING \| EXCLUDING } { DEFAULTS \| CONSTRAINTS \| INDEXES \| STORAGE \| COMMENTS \| PARTITION \| RELOPTIONS \| DISTRIBUTION \| ALL }

其中RANGE分布规则
 slice_less_than_item为：

SLICE slice_name VALUES LESS THAN ({ literal | MAXVALUE } [, ...]) [ DATANODE dn_name ]

slice_start_end_item为：

SLICE slice_name_prefix {

{ START ( literal ) END ( literal ) EVERY ( literal ) } |

{ START ( literal ) END ( { literal | MAXVALUE } ) } |

{ START ( literal ) } |

{ END ( { literal | MAXVALUE } ) }

}
其中LIST分布规则slice_values_item为：
SLICE slice_name VALUES (list_values_item) [DATANODE dn_name]

list_values_item为：

{ DEFAULT | { partition_values_list [, ...] } }

partition_values_list为:

{ (literal [, ...]) }