CREATE TABLE
功能描述
在当前数据库中创建一个新的空白表，该表由命令执行者所有。
注意事项
- 如果在建表过程中数据库系统发生故障，系统恢复后可能无法自动清除之前已创建的、大小为0的磁盘文件。此种情况出现概率小，不影响数据库系统的正常运行。
- 使用JDBC时，支持通过PrepareStatement对DEFAULT值进行参数化设置。
- 被授予CREATE ANY TABLE权限的用户，可以在public模式和用户模式下创建表。
- 主键不支持前缀索引。
- 分布式当前不支持外键约束。
- 若使用字符类型的分布列，不建议使用不区分大小写的字符序（如：utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_0900_ai_ci），容易造成数据倾斜的问题，无法平均分布。推荐使用utf8mb4_bin字符序。
语法格式
CREATE [ [ [LOCAL | GLOBAL] TEMPORARY | UNLOGGED] ] TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset_name ] [ compress_mode ] [ COLLATE collation_name][COMMENT [=] 'string'] [ column_constraint [ ... ] ]
| table_constraint
| {INDEX | KEY} [ [schema_name.] index_name ] [ USING method ] ({column_name [(length)] | (expr) [ASC | DESC]}[,...]) [[COMMENT 'string' | USING method][...]]
| LIKE source_table [ like_option [...] ] }
[, ... ])
[ table_option ][[,...]|[...]]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY
{ REPLICATION
| HASH ( column_name [, ...] )
| RANGE ( column_name [, ...] ) { SLICE REFERENCES tablename | ( slice_less_than_item [, ...] )
| LIST ( column_name [, ...] ) { SLICE REFERENCES tablename | ( slice_values_item [, ...] ) }
}
[ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
[ INTERNAL DATA data_content];
- 从已经存在的源表创建新表
CREATE [ [LOCAL | GLOBAL] TEMPORARY ] TABLE [IF NOT EXISTS] table_name LIKE source_table;
该语法会默认把源表的属性搬到新创建的表，包括列定义，索引，分布分区方式，存储定义，注释等，这也适用于GaussDB对于分布式下高级增强能力的适配，如全局二级索引特性。
- 其中table_option为：
{ [DEFAULT] {CHARACTER SET | CHAR SET | CHARSET} [=] charset_name | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | ENGINE [=] engine_name | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} }
- 同一个地方多次设置COMMENT时仅最后一个生效。
- 同一个地方多次设置COLLATE时仅最后一个生效。
- ENGINE、ROW_FORMAT使用时语法不报错也没有提示，但实际不生效。
- 不支持WITH (ORIENTATION = column)。
- 不支持WITH (HASHBUCKET = ON/OFF)。
- 其中列约束column_constraint为：
[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | ON UPDATE now_expr | UNIQUE [KEY] index_parameters | [ PRIMARY ] KEY index_parameters | }
- 其中列的压缩可选项compress_mode为：
{ DICTIONARY } - 其中表约束table_constraint为：
[ CONSTRAINT [ constraint_name ] ] { CHECK ( expression ) | UNIQUE [ INDEX | KEY ] [ index_name ][ USING method ] ( { { column_name [ ( length ) ] | （ expression） } [ ASC | DESC ] } [, ... ] ) index_parameters [USING method| COMMENT 'string']| PRIMARY KEY [index_name][ USING method ] ( { column_name }[ ASC | DESC ] } [, ... ] ) index_parameters [USING method| COMMENT 'string']| }
- 其中索引参数index_parameters为：
[ WITH ( {storage_parameter = value} [,...] [ USING INDEX TABLESPACE tablespace_name ] - 其中like选项like_option为：
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | UPDATE | ALL } - 其中RANGE分布规则
slice_less_than_item为： SLICE slice_name VALUES LESS THAN ({ literal | MAXVALUE } [, ...]) [ DATANODE dn_name | ( dn_name [, ... ] ) ] slice_start_end_item为： SLICE slice_name_prefix { { START ( literal ) END ( { literal | MAXVALUE } ) } | { START ( literal ) } | { END ( { literal | MAXVALUE } ) } } - 其中LIST分布规则
slice_values_item为： [SLICE slice_name VALUES (list_values_item) [DATANODE dn_name | ( dn_name [, ... ] ) ] list_values_item为： { DEFAULT | { expression[, ...] } } - 其中now_expr为：
{ CURRENT_TIMESTAMP | LOCALTIMESTAMP | LOCALTIME | NOW() }
参数说明
- UNLOGGED
如果指定此关键字，则创建的表为非日志表。
- 在非日志表中写入的数据不会被写入到预写日志中，当执行操作系统重启、数据库重启、主备切换、切断电源操作或异常关机后，非日志表上的写入会被自动截断，会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。
- 备服务器上非日志表无数据，且在备机查询非日志表时会报错。
使用场景：非日志表无法保证数据的安全性，用户应该在确保数据已经做好备份的前提下使用，例如系统升级时进行数据的备份。
故障处理：当异常关机等操作导致非日志表上的索引发生数据丢失时，用户应该对发生错误的索引进行重建。
- GLOBAL | LOCAL
创建临时表时可以在TEMPORARY前指定GLOBAL或LOCAL关键字。如果指定GLOBAL关键字，会报WARNING：GLOBAL is deprecated in temporary table creation. M-Compatibility会创建本地临时表。
- TEMPORARY
如果指定TEMPORARY关键字，则创建的表为临时表。
临时表只在当前会话可见，本会话结束后会自动删除。因此，在除当前会话连接的CN以外的其他CN故障时，仍然可以在当前会话上创建和使用临时表。由于临时表只在当前会话创建，对于涉及对临时表操作的DDL语句，会产生DDL失败的报错。因此，建议DDL语句中不要对临时表进行操作。
- 本地临时表通过每个会话独立的以pg_temp开头的schema来保证只对当前会话可见，因此，不建议用户在日常操作中手动删除以pg_temp、pg_toast_temp开头的schema。
- 如果建表时不指定TEMPORARY关键字，而指定表的schema为当前会话的pg_temp_开头的schema，则此表会被创建为临时表。
- ALTER/DROP全局临时表和索引，如果其它会话正在使用它，禁止操作。
- 全局临时表的DDL只会影响当前会话的用户数据和索引。例如truncate、reindex、analyze只对当前会话有效。
- 全局临时表功能可以通过设置GUC参数max_active_global_temporary_table控制是否启用。如果max_active_global_temporary_table=0，关闭全局临时表功能。
- 临时表只对当前会话可见，因此不支持与\parallel on并行执行一起使用。
- 临时表不支持主备切换。
- 全局临时表不响应自动清理，在长连接场景使用时尽量使用on commit delete rows的全局临时表，或定期手动执行vacuum，否则可能导致clog日志不回收。
- 全局临时表不支持以下场景：
- 不支持创建全局临时Sequence，各个会话的全局临时表使用共享的Sequence，只能保证唯一性，不保证连续性。
- 不支持创建全局临时视图。
- 不支持创建分区表。
- 不支持创建Hash bucket表。
- 不支持扩展统计信息。
- IF NOT EXISTS
如果已经存在相同名称的表，不会报出错误，而会发出通知，告知通知此表已存在。
- table_name
要创建的表名。
- column_name
新表中要创建的字段名。
- constraint_name
建表时指定的约束名称。
- index_name
索引名。
对于唯一键约束，constraint_name和index_name同时指定时，索引名为index_name。
- USING method
指定创建索引的方法。
取值范围请参见参数说明中的USING method。
目前只支持索引方法为btree，但是不支持ustore表创建btree索引。
- ASC | DESC
ASC表示指定按升序排序（默认）。DESC指定按降序排序。
- expression
创建一个基于该表的一个或多个字段的表达式索引约束，必须写在圆括弧中。
- data_type
字段的数据类型。
- compress_mode
表字段的压缩选项。该选项指定表字段优先使用的压缩算法。行存表不支持压缩。当前M-Compatibility模式数据库下无法使用。
取值范围：DICTIONARY
- {CHARACTER SET | CHAR SET | CHARSET} charset_name
指定表字段的字符集。单独指定时会将字段的字符序设置为指定的字符集的默认字符序。
- COLLATE collation_name
COLLATE子句指定列的排序规则（字符序）（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“select * from pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。支持utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci、binary、gbk_chinese_ci、gbk_bin、gb18030_chinese_ci、gb18030_bin字符序。具体请参见表级字符集和字符序
- 仅字符类型支持指定字符集。指定为BINARY字符集或字符序实际是将字符类型转化为对应的二进制类型，若类型映射不存在则报错。当前仅有TEXT类型转化为BLOB的映射。
- 除BINARY字符集、字符序外，当前仅支持指定与数据库编码相同的字符集。
- 字段字符集或字符序未显式指定时，若指定了表的默认字符集或字符序，字段字符集和字符序将从表上继承。
表1 支持的字符集和字符序介绍 字符序名称
对应的字符集
描述
utf8mb4_general_ci
utf8mb4（即utf8）
使用通用排序规则，不区分大小写。
utf8mb4_unicode_ci
utf8mb4（即utf8）
使用通用排序规则，不区分大小写。
utf8mb4_bin
utf8mb4（即utf8）
使用二进制排序规则，区分大小写。
binary
binary
使用二进制排序规则。
gbk_chinese_ci
gbk
使用中文语言排序规则。
gbk_bin
gbk
使用二进制排序规则，区分大小写。
gb18030_chinese_ci
gb18030
使用中文语言排序规则。
gb18030_bin
gb18030
使用二进制排序规则，区分大小写。
utf8mb4_0900_ai_ci
utf8mb4
使用UCA (Unicode collation algorithm) 算法规则，不区分大小写。
utf8_general_ci
utf8
使用通用排序规则，不区分大小写。
utf8_bin
utf8
使用二进制排序规则，区分大小写。
- LIKE source_table [like_option...]
LIKE子句声明一个表，新表自动从这个表中继承所有字段名及其数据类型和约束以及索引等。
新表与源表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会在新表中产生变化，并且在扫描源表的时候也不会包含新表的数据。
- 被复制的列和约束并不使用相同的名称进行融合。如果明确的指定了相同的名称或者在另外一个LIKE子句中，将会报错。
- 源表的非空约束将会复制到新表中。
- 指定INCLUDING选项时，将从源表上复制相关内容到新表中；指定EXCLUDING选项时，行为与指定INCLUDING选项相反。
- 如果指定了INCLUDING DEFAULTS，则源表上的默认值会复制到新表中。
- 如果指定了INCLUDING UPDATE，则源表列的ON UPDATE now_expr属性会复制到新表列中。
- 如果指定了INCLUDING CONSTRAINTS，则源表上的CHECK约束会复制到新表中。
- 如果指定了INCLUDING INDEXES，则源表上的索引也将在新表上创建。
- 如果指定了INCLUDING STORAGE，则源表列的STORAGE设置会复制到新表中。
- 如果指定了INCLUDING COMMENTS，则源表列、约束和索引的注释会复制到新表中。
- 如果指定了INCLUDING PARTITION，则源表的分区定义会复制到新表中。
- 如果指定了INCLUDING RELOPTIONS，则源表的存储参数（即源表的WITH子句）会复制到新表中。
- 如果指定了INCLUDING DISTRIBUTION，则源表的分布信息会复制到新表中，包括分布类型和分布列。
- 未指定INCLUDING或EXCLUDING选项时，行为等同于指定INCLUDING ALL选项，INCLUDING ALL包含了INCLUDING DEFAULTS、INCLUDING UPDATE、INCLUDING CONSTRAINTS、INCLUDING INDEXES、INCLUDING STORAGE、INCLUDING COMMENTS、INCLUDING PARTITION、INCLUDING RELOPTIONS和INCLUDING DISTRIBUTION的内容。
不支持从临时表中创建正常表。
如果建表时包括LIKE子句和WITH子句、PARTITION BY子句、DISTRIBUTE BY子句时，建表将会使用SQL语句中指定的WITH子句信息、分区信息以及分布信息，而不再使用源表的信息。对于CHARSET、COMMENT等信息，同时指定时使用源表的信息。
- COMMENT [ = ] 'string'
- COMMENT [ = ] 'string'子句表示给表添加注释。
- 在column_constraint中的COMMENT 'string'表示给列添加注释。
- 在table_constraint中的COMMENT 'string'表示给主键和唯一键对应的索引添加注释。
- 表级注释支持的最大字符串长度为2048字符，列级和索引级注释支持的最大长度为1024字符。
- table_constraint中的COMMENT仅支持主键和唯一键，其他约束不支持。
- WITH ( { storage_parameter = value } [, ... ] )
这个子句为表或索引指定一个可选的存储参数。用于表的WITH子句还可以包含OIDS=FALSE表示不分配OID。
参数的详细描述如下所示。
- FILLFACTOR
一个表的填充因子（fillfactor）是一个介于10和100之间的百分数。在Ustore存储引擎下，该值得默认值为92，在Astore存储引擎下默认值为100（完全填充）。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是最佳选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。
取值范围：10~100
- ORIENTATION
指定表数据的存储方式，该参数设置成功后就不再支持修改，当前M-Compatibility仅支持行存方式。
取值范围：
默认值：
若指定表空间为普通表空间，默认值为ROW。
- STORAGE_TYPE
指定存储引擎类型，该参数设置成功后就不再支持修改。
取值范围：
- USTORE，表示表支持Inplace-Update存储引擎。特别需要注意，使用UStore表，必须要开启track_counts和track_activities参数，否则会引起空间膨胀。
- ASTORE，表示表支持Append-Only存储引擎。
默认值：
不指定ORIENTATION和STORAGE_TYPE时创建表，默认是USTORE存储引擎（表示表支持Inplace-Update存储引擎）。
- INIT_TD
创建UStore表时，指定初始化的TD个数，该参数可以通过alter table进行修改。特别需要注意，该参数会影响数据页面存放的单个元组的最大大小，具体换算方法为MAX_TUPLE_SIZE = BLCKSZ - INIT_TD * TD_SIZE，例如用户将INIT_TD数量从4修改为8，单个元组最大大小会减小4 * INIT_TD大小。
取值范围：2~128，默认值为4。
- COMPRESSION
指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。
取值范围：行存表不支持压缩，默认值为NO。
- COMPRESSLEVEL
指定表数据同一压缩级别下的不同压缩水平，它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分，为用户选择压缩比和压缩时间提供了更多的空间。总体来讲，此值越大，表示同一压缩级别下压缩比越大，压缩时间越长；反之亦然。
取值范围：0~3，默认值为0。
- segment
使用段页式的方式存储。本参数仅支持行存表。不支持1-5号物理文件非法删除破坏场景的防护。
取值范围：on/off
默认值：off
- enable_tde
指定该表为加密表。数据库会自动将加密表中的数据先加密再存储。使用该参数前，请确保已通过GUC参数enable_tde开启透明加密功能，并通过GUC参数tde_key_info设置访问密钥服务的信息，在《特性指南》中“透明数据加密”章节可获取该参数的详细使用方法。本参数仅支持行存表、段页式表、临时表和unlogged表。
取值范围：on/off。设置enable_tde=on时，key_type、tde_cmk_id、dek_cipher参数由数据库自动生成，用户无法手动指定或更改。
默认值：off
- parallel_workers
表示创建索引时起的bgworker线程数量，例如2就表示将会起2个bgworker线程并发创建索引。
取值范围：[0,32]，int类型，0表示关闭并行建索引。
默认值：不设置该参数，表示未开启并行建索引功能。
- encrypt_algo
取值范围：字符串，有效值为：AES_128_CTR，SM4_CTR。
默认值：为tde_encrypt_config参数中table_algorithm的赋值。
- parallel_workers
表示创建索引时起的bgworker线程数量，例如2就表示将会起2个bgworker线程并发创建索引。
取值范围：[0,32]，int类型，0表示关闭并行建索引。
默认值：不设置该参数，表示未开启并行建索引功能。
- dek_cipher
数据密钥的密文。用户为表设置enable_tde参数后，数据库自动生成数据密钥。
取值范围：字符串
默认值：空
- key_type
主密钥的类型。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的类型。
取值范围：字符串
默认值：空
- cmk_id
主密钥的ID。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的ID。
取值范围：字符串
默认值：空
- hasuids
参数开启：更新表元组时，为元组分配表级唯一标识id。
取值范围：on/off。
默认值：off。
- collate
用于记录表的默认字符序，一般只用于内部存储和导入导出，不推荐用户指定或修改。
取值范围：支持的字符序的oid。
默认值：0。
- stat_state
标识该表的统计信息是否被锁定，如果被锁定了，该表的统计信息无法更新。
取值范围：locked、unlock。
默认值：unlock。
目前，505.2分布式环境不支持压缩算法, 不支持COMPRESSTYPE，COMPRESS_LEVEL， COMPRESS_CHUNK_SIZE， COMPRESS_PREALLOC_CHUNKS，COMPRESS_BYTE_CONVERT和COMPRESS_DIFF_CONVERT选项。
- FILLFACTOR
- ON COMMIT { PRESERVE ROWS | DELETE ROWS }
ON COMMIT选项决定在事务中执行创建临时表操作，当事务提交时，此临时表的后续操作。有以下两个选项，当前支持PRESERVE ROWS和DELETE ROWS选项。
- PRESERVE ROWS（缺省值）：提交时不对临时表做任何操作，临时表及其表数据保持不变。
- DELETE ROWS：提交时删除临时表中数据。
- TABLESPACE tablespace_name
创建新表时指定此关键字，表示新表将要在指定表空间内创建。如果没有声明，将使用默认表空间。
注：需要在非M-Compatibility数据库中创建或删除tablespace。
- CONSTRAINT constraint_name
列约束或表约束的名称。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。
定义约束有两种方法：
- 列约束：作为一个列定义的一部分，仅影响该列。
- 表约束：不和某个列绑在一起，可以作用于多个列。
- NOT NULL
字段值不允许为NULL。
- NULL
字段值允许为NULL ，这是缺省值。
这个子句只是为和非标准SQL数据库兼容。不建议使用。
- CHECK ( expression )
CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。
expression表达式中，如果存在“<>NULL”或“！=NULL”，这种写法是无效的，需要写成“is NOT NULL”。
- DEFAULT default_expr
- 使用DEFAULT子句给字段指定缺省表达式，缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。
- 支持指定以下内容：常量、带正负号的数值常量、update_expr。
- 仅支持在TIMESTAMP、DATETIME类型的字段上指定update_expr作为默认值，且字段的精度与update_expr的精度须保持一致。
- ON UPDATE now_expr
ON UPDATE子句为字段的一种属性约束。
当对表中某元组执行UPDATE操作时，若更新字段的新值和表中旧值不相同，则表中该元组上具有该属性且不在更新字段内的字段值自动更新为当前时间戳；若更新字段的新值和表中旧值相同，则表中该元组上具有该属性且不在更新字段内的字段值不变，保持原有值；若具有该属性的字段在更新字段内，则对应这些字段值直接按指定更新的值更新。
- 语法上update_expr支持CURRENT_TIMESTAMP 、LOCALTIMESTAMP 、LOCALTIME、NOW()四种关键字，也支持关键字带括号指定或不指定精度。例如：ON UPDATE CURRENT_TIMESTAMP()、ON UPDATE CURRENT_TIMESTAMP(5)、ON UPDATE LOCALTIMESTAMP()、ON UPDATE LOCALTIMESTAMP(6)等。不带括号或空括号时精度为0，其中NOW关键字不支持不带括号。四种关键字互为同义词，属性效果相同。
- 该属性支持在如下类型的列上指定: TIMESTAMP、DATETIME。
- 该属性指定的精度和对应列上类型指定的精度必须一致，否则会触发报错。例如：CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6)); 若精度不一致，会产生ERROR: Invalid ON UPDATE clause for "col1" 报错。
- 该属性和生成列约束不能同时指定同一列。
- 分区表中的分区键不支持指定该属性。
- [DEFAULT] {CHARACTER SET | CHAR SET | CHARSET} [ = ] default_charset
指定表的默认字符集。单独指定时会将表的默认字符序设置为指定的字符集的默认字符序。
- [DEFAULT] COLLATE [ = ] default_collation
指定表的默认字符序。单独指定时会将表的默认字符集设置为指定的字符序对应的字符集。
字符序请参见字符集与字符序。
表的字符集或字符序未显式指定时，若指定了模式的默认字符集或字符序，表字符集和字符序将从模式上继承。
- 列级唯一约束：UNIQUE [KEY] index_parameters
UNIQUE约束表示表里的一个或多个字段的组合必须在全表范围内唯一。
UNIQUE KEY与UNIQUE语义相同。
- 表级唯一约束：UNIQUE [INDEX | KEY][ index_name ][ USING method ]( {{ column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters
UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。
对于唯一约束，NULL被认为是互不相等的。
column_name(length)是前缀键，详见：•column_name ( length )。
index_name为索引名。
对于唯一键约束，constraint_name和index_name同时指定时，索引名为index_name。
- 列级主键约束：[PRIMARY] KEY index_parameters
表级主键约束：PRIMARY KEY [index_name] [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ） index_parameters
主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。
一个表只能声明一个主键。
- USING INDEX TABLESPACE tablespace_name
为UNIQUE或PRIMARY KEY约束相关的索引声明一个表空间。如果没有提供这个子句，这个索引将在default_tablespace中创建，如果default_tablespace为空，将使用数据库的缺省表空间。
该属性在数据库M-compatibility模式兼容版本控制开关为s1及以上版本时不支持（如m_format_dev_version = 's1'）。
- DISTRIBUTE BY
指定表如何在节点之间分布或者复制。
取值范围：
- REPLICATION：表的每一行存在所有数据节点（DN）中，即每个数据节点都有完整的表数据。
- HASH ( column_name ) ：对指定的列进行Hash，通过映射，把数据分布到对应DN。
- RANGE( column_name ) 对指定列按照范围进行映射，把数据分布到对应DN。
- LIST( column_name ) 对指定列按照具体值进行映射，把数据分布到对应DN。
- 对于HASH分布，分布键最多个数和列最多个数一致，最多支持1600个。对于RANGE（VALUE LESS THAN）分布和LIST分布，分布键最多支持四列。对于RANGE（START END）分布，分布键只支持一列。
- 对于从句是VALUE LESS THAN语法格式的RANGE分布策略。分布规则如下：
- 从插入值的第一列开始比较。
- 如果插入值的第一列小于待插入的分片的当前列的边界值，则直接插入。
- 如果插入值的第一列等于待插入的分片的当前列的边界值，则比较插入值的下一列与待插入的分片的下一列的边界值，如果小于，则直接插入。如果相等，继续比较插入值的下一列与待插入的分片的下一列的边界值，直至小于并插入。
- 如果插入值的所有列大于待插入的分片的所有列的边界值，则比较下一分片。
- 如果RANGE分布表的分片对应多个DN，会计算分布键的hash值，之后在这些DN中用hash值对DN个数取模，重新映射一个DN。详情见示例。
- 如果LIST分布表的分片对应多个DN，对于default分片，会计算分布键的hash值，之后在这些DN中用hash值对DN个数取模，重新映射一个DN。对于非default分片，会使用Round Robin方式，把values列表中的值和DN进行映射。详情见示例。
- RANGE/LIST分布表只支持扩容不支持缩容，slice扩容规则请联系管理员。
对于HASH分布，column_name的数据类型必须是以下类型之一：- INTEGER TYPES：TINYINT、SMALLINT、INTEGER、MEDIUMINT、BIGINT、TINYINT UNSIGNED, SMALLINT UNSIGNED, INTEGER UNESIGNED, MEDIUMINT UNSIGNED、BIGINT UNSIGNED、NUMERIC[(p[,s])]、DECIMAL[(p[,s])]
- CHARACTER TYPES：CHAR[(n)]、VARCHAR[(n)]、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
- DATE/TIME TYPES：DATE、TIME[(p)]、TIMESTAMP[(p)], DATETIME[(p)], YEAR
对于RANGE（VALUES LESS THAN）/LIST分布，column_name的数据类型必须是以下类型之一：- INTEGER TYPES：TINYINT、SMALLINT、INT、MEDIUMINT、BIGINT、TINYINT UNSIGNED, SMALLINT UNSIGNED, INTEGER UNESIGNED, MEDIUMINT UNSIGNED、BIGINT UNSIGNED、NUMERIC[(p[,s])]、DECIMAL[(p[,s])]
- CHARACTER TYPES：CHAR[(n)]、VARCHAR[(n)]、BINARY[(n)]、VARBINARY(n)、TINYTEXT、 TEXT、 MEDIUMTEXT、 LONGTEXT
- DATE/TIME TYPES：DATE、TIMESTAMP[(p)]、DATETIME[(p)]、YEAR
对于RANGE（START END）分布，column_name的数据类型必须是以下类型之一：
- INTEGER TYPES：TINYINT、SMALLINT、INT、MEDIUMINT、BIGINT、TINYINT UNSIGNED, SMALLINT UNSIGNED, INTEGER UNESIGNED, MEDIUMINT UNSIGNED、BIGINT UNSIGNED、NUMERIC[(p[,s])]、DECIMAL[(p[,s])]
- DATE/TIME TYPES：DATE、TIMESTAMP[(p)]、DATETIME[(p)]、YEAR
在建表时，选择分布列和分区键可对SQL查询性能产生重大影响。因此，需要根据一定策略选择合适的分布列和分区键。
- 选择合适的分布列
对于采用散列（Hash）方式的数据分布表，一个合适的分布列应将一个表内的数据，均匀分散存储在多个DN内，避免出现数据倾斜现象（即多个DN内数据分布不均）。请按照如下原则判定合适的分布列：
- 判断是否已发生数据倾斜现象。
连接数据库，执行如下语句，查看各DN内元组数目。命令中的斜体部分tablename，请填入待分析的表名。
gaussdb=# SELECT a.count,b.node_name FROM (SELECT count(*) AS count,xc_node_id FROM tablename GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count DESC;如果各DN内元组数目相差较大（如相差数倍、数十倍），则表明已发生数据倾斜现象，请按照下面原则调整分布列。
- 重新选择分布列，重新建表。当前不支持通过ALTER TABLE语句调整分布列，因此调整分布列时需要重新建表。
分布列的列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。
在满足上面原则的情况下，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。
- 判断是否已发生数据倾斜现象。
- 选择合适的分区键
数据分区功能，可根据表的一列或者多列，将要插入表的记录分为若干个范围（这些范围在不同的分区里没有重叠）。然后为每个范围创建一个分区，用来存储相应的数据。
调整分区键，使每次查询结果尽可能存储在相同或者最少的分区内（称为“分区剪枝”），通过获取连续I/O大幅度提升查询性能。
实际业务中，经常将时间作为查询对象的过滤条件，因此，可考虑选择时间列为分区键，键值范围可根据总数据量、一次查询数据量调整。
- RANGE/LIST分布
当没有为RANGE/LIST分布表的分片显示指定DN时，数据库内部为分片分配DN是采用roundrobin的算法。另外，在使用RANGE/LIST分布的场景中，考虑到后续扩容的需要，建议用户在建表时定义尽可能多的分片数，因为如果定义的分片数小于扩容前的DN节点数，数据重分布时则无法落入新的DN节点。需要特别注意的是，由于是由用户自行设计分片规则，在某些极端情况下，扩容也可能无法解决存储空间不足的问题。
- TO { GROUP groupname | NODE ( nodename [, ... ] ) }
TO GROUP指定创建表所在的Node Group。TO NODE主要供内部扩容工具使用，一般用户不应该使用。
- INTERNAL DATA data_content
INTERNAL DATA data_content仅扩容内部用，一般用户不应该使用。
建表示例
- 临时表
--创建表临时表。 m_db=# CREATE TEMPORARY TABLE test_t1( id CHAR(7), name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 ); -- 在当前会话中插入数据。 m_db=# INSERT INTO test_t1 VALUES ('0000009','Jack','Guangzhou','China'); --临时表里面的数据只在当前事务中有效，所以在另一个会话中查看该表中没有数据。 m_db=# SELECT * FROM test_t1; id | name | age ----+------+----- (0 rows)--事务中创建表临时表，并指定提交事务时删除该临时表数据。 m_db=# START TRANSACTION; m_db=# CREATE TEMPORARY TABLE test_t2( id CHAR(7), name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 ) ON COMMIT DELETE ROWS; m_db=# INSERT test_t2 VALUES('aid','aname','aprovince','China'); m_db=# COMMIT; m_db=# SELECT * FROM test_t2; id | name | province | country ----+------+----------+--------- (0 rows) --删除表。 m_db=# DROP TABLE test_t1; m_db=# DROP TABLE test_t2; - IF NOT EXIST关键字
使用该关键字，表不存在时报NOTICE；如不用该关键字，则报ERROR。两种情况下表都不会创建成功。
m_db=# CREATE TABLE test_t3(id INT); --创建一个已经存在同名的表test_t3。 m_db=# CREATE TABLE test_t3(id INT); ERROR: relation "test_t3" already exists in schema "public" DETAIL: creating new table with existing name in the same schema --使用IF NOT EXISTS关键字。 m_db=# CREATE TABLE IF NOT EXISTS test_t3(id INT); NOTICE: relation "test_t3" already exists, skipping CREATE TABLE --删除表。 m_db=# DROP TABLE test_t3;
- CREATE TABLE ... LIKE建表
-- 创建源表t1 m_db=# CREATE TABLE t1(col INT); CREATE TABLE m_db=# \d t1 Table "public.t1" Column | Type | Modifiers --------+---------+----------- col | integer | -- 创建目标表t2 m_db=# CREATE TABLE t2(LIKE t1); CREATE TABLE m_db=# \d t2 Table "public.t2" Column | Type | Modifiers --------+---------+----------- col | integer | -- 创建目标表t3 m_db=# CREATE TABLE t3 LIKE t1; CREATE TABLE m_db=# \d t3 Table "public.t3" Column | Type | Modifiers --------+---------+----------- col | integer |
建表添加约束示例
- 非空约束
非空约束的字段，如果在添加数据时没有指定值，就会报错。可以为表中多个字段添加非空约束。
--建表并给id字段添加非空约束。 m_db=# CREATE TABLE test_t4( id CHAR(7) NOT NULL, name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 ); --插入数据时,如果不指定id的值或者id为NULL，会触发非空约束,导致插入失败。 m_db=# INSERT INTO test_t4 (name,province) VALUES ('scott','Shanghai'); ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, scott, Shanghai, China) --删除表。 m_db=# DROP TABLE test_t4; - 唯一约束
关键字UNIQUE给字段添加一个唯一约束，插入数据时该字段如有重复则触发约束，多个NULL不算重复，添加唯一约束时，会自动增加一个唯一索引。可以为表中多个字段添加唯一约束。
--建表添加唯一约束。 m_db=# CREATE TABLE test_t5( id CHAR(7) UNIQUE, name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 ); --也可以用如下写法，人工为唯一约束命名，以及为多个字段添加约束。 m_db=# CREATE TABLE test_t6( id CHAR(7), name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China', --国籍 CONSTRAINT unq_test_id UNIQUE (id,name) );--插入id重复的数据，触发约束，导致插入失败。 m_db=# INSERT INTO test_t5(id) VALUES('0000010'); INSERT 0 1 m_db=# INSERT INTO test_t5(id) VALUES('0000010'); ERROR: duplicate key value violates unique constraint "test_t5_id_key" DETAIL: Key (id)=(0000010) already exists.--多次插入id是NULL的数据不会触发约束。 m_db=# INSERT INTO test_t5(id) VALUES (NULL); INSERT 0 1 m_db=# INSERT INTO test_t5(id) VALUES (NULL); INSERT 0 1 m_db=# SELECT * FROM test_t5; id | name | province | country ---------+------+----------+-------- 0000010 | | | China | | | China | | | China --删除表。 m_db=# DROP TABLE test_t5; m_db=# DROP TABLE test_t6; - 主键约束
关键字PRIMARY KEY给字段添加主键约束，要求字段唯一且不为空。添加主键约束时自动为该表创建唯一索引，也会为该字段自动增加一个非空约束。
每个表里面只能定义一个主键约束，不能定义多个。
--建表添加主键约束。 m_db=# CREATE TABLE test_t6( id CHAR(7) PRIMARY KEY, name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 ); m_db=# INSERT INTO test_t6 (id,name,province) VALUES ('0000001','july','Beijing'); --也可以用如下写法，人工为唯一约束命名，以及为多个字段添加约束。 m_db=# CREATE TABLE test_t7( id CHAR(7), name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China', --国籍 CONSTRAINT pk_test_t6_id PRIMARY KEY (id,name) );--插入id为NULL的数据，触发约束。 m_db=# INSERT INTO test_t6 (id,name,province) VALUES (NULL,'july','Beijing'); ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, july, Beijing, China). --插入id重复的数据，触发约束。 m_db=# INSERT INTO test_t6 (id,name,province) VALUES ('0000001','ben','Shanghai'); ERROR: duplicate key value violates unique constraint "test_t6_pkey" DETAIL: Key (id)=(0000001) already exists.--删除表。 m_db=# DROP TABLE test_t6; m_db=# DROP TABLE test_t7;
- 检查约束
关键字CHECK给字段添加检查约束，在检查约束中必须引用表中的一个或多个字段，并且表达式返回结果必须是一个布尔值。在表达式中不能包含子查询。对同一个字段可以同时定义检查约束和非空约束。
--建表，添加检查约束。 m_db=# CREATE TABLE test_t8 ( id CHAR(7), name VARCHAR(20), age INT CHECK(age > 0 AND age < 150) ); --也可以使用如下SQL语句，人工为检查约束命名以及为一个或者多个字段添加检查约束。 m_db=# CREATE TABLE test_t9 ( id CHAR(7), name VARCHAR(20), age INT, CONSTRAINT chek_test_t8_age CHECK(age > 0 AND age < 150) );--插入不符合表达式的值，会触发检查约束导致插入失败。 m_db=# INSERT INTO test_t8 (id,name,age) VALUES ('0000007','scott',200); ERROR: new row for relation "test_t8" violates check constraint "test_t8_age_check" DETAIL: N/A--删除表。 m_db=# DROP TABLE test_t8; m_db=# DROP TABLE test_t9;
表数据分布示例
- REPLICATION
gaussdb=# CREATE TABLE test_replication( id CHAR(7), name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 )DISTRIBUTE BY REPLICATION; --查询表信息。 gaussdb=# \d+ test_replication Table "public.test_replication" Column | Type | Modifiers | Storage | Stats target | Description ----------+-----------------------+------------------------------------+----------+--------------+------------- id | character(7) | | extended | | name | character varying(20) | | extended | | province | character varying(60) | | extended | | country | character varying(30) | default 'China'::character varying | extended | | Has OIDs: no Distribute By: REPLICATION Location Nodes: ALL DATANODES Options: orientation=row, logical_repl_node=-1, compression=no --删除。 gaussdb=# DROP TABLE test_replication;
- HASH
--定义一个表，使用HASH分布。 gaussdb=# CREATE TABLE test_hash( id CHAR(7), name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 )DISTRIBUTE BY HASH(id); --插入数据。 gaussdb=# INSERT INTO test_hash VALUES ('0000001', 'Bob', 'Shanghai', 'China'), ('0000002', 'Jack', 'Beijing', 'China'), ('0000003', 'Scott', 'Beijing', 'China');
--查看数据分布情况。 gaussdb=# SELECT a.count,b.node_name FROM (SELECT COUNT(*) AS count, xc_node_id FROM test_hash GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count DESC; count | node_name -------+------------------- 2 | dn_6001_6002_6003 1 | dn_6004_6005_6006--删除表。 gaussdb=# DROP TABLE test_hash; - RANGE
--定义一个表，使用RANGE分布（需要根据实际情况修改dn名字，查询dn节点名可以通过语句SELECT node_name FROM PGXC_NODE WHERE node_type = 'D';查询）。 gaussdb=# CREATE TABLE test_range( id INT, name VARCHAR(20), province VARCHAR(60), --省 country VARCHAR(30) DEFAULT 'China' --国籍 )DISTRIBUTE BY RANGE(id)( SLICE s1 VALUES LESS THAN (100) DATANODE dn_6001_6002_6003, SLICE s2 VALUES LESS THAN (200) DATANODE dn_6004_6005_6006, SLICE s3 VALUES LESS THAN (MAXVALUE) DATANODE dn_6007_6008_6009 ); --插入数据。 gaussdb=# INSERT INTO test_range VALUES (52, 'Bob', 'Beijing', 'China'); gaussdb=# INSERT INTO test_range VALUES (100, 'Ben', 'Shanghai', 'China'); gaussdb=# INSERT INTO test_range VALUES (150, 'Scott', 'Guangzhou', 'China'); gaussdb=# INSERT INTO test_range VALUES (300, 'Jordan', 'Beijing', 'China');
--查看数据分布情况。 gaussdb=# SELECT a.count,b.node_name FROM (SELECT COUNT(*) AS count, xc_node_id FROM test_range GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count DESC; count | node_name -------+------------------- 2 | dn_6004_6005_6006 1 | dn_6001_6002_6003 1 | dn_6007_6008_6009 (3 rows)--查询各dn上存储的数据。 gaussdb=# SELECT b.node_name, a.* FROM (SELECT *, xc_node_id FROM test_range) a, pgxc_node b WHERE a.xc_node_id=b.node_id order by node_name; node_name | id | name | province | country | xc_node_id -------------------+-----+--------+-----------+---------+------------- dn_6001_6002_6003 | 52 | Bob | Beijing | China | -1072999043 dn_6004_6005_6006 | 100 | Ben | Shanghai | China | -564789568 dn_6004_6005_6006 | 150 | Scott | Guangzhou | China | -564789568 dn_6007_6008_6009 | 300 | Jordan | Beijing | China | 1532339558 (4 rows)--删除表。 gaussdb=# DROP TABLE test_range; - LIST
--定义一个表，使用LIST分布（需要根据实际情况修改dn名字，查询dn节点名可以通过语句SELECT node_name FROM PGXC_NODE WHERE node_type = 'D';查询）。 gaussdb=# CREATE TABLE test_list( id INT, name VARCHAR(20), country VARCHAR(30) DEFAULT 'China' --国籍 )DISTRIBUTE BY LIST(country)( SLICE s1 VALUES ('China') DATANODE dn_6001_6002_6003, SLICE s2 VALUES ('USA') DATANODE dn_6004_6005_6006, SLICE s3 VALUES (DEFAULT) DATANODE dn_6007_6008_6009 ); --插入数据。 gaussdb=# INSERT INTO test_list VALUES (1,'Scott','China'); gaussdb=# INSERT INTO test_list VALUES (2,'Henry','USA'); gaussdb=# INSERT INTO test_list VALUES (3,'Michael','France'); gaussdb=# INSERT INTO test_list VALUES (4,'Jack','UK');
--查询各dn上存储的数据。 gaussdb=# SELECT b.node_name, a.* FROM (SELECT *, xc_node_id FROM test_list) a, pgxc_node b WHERE a.xc_node_id=b.node_id order by node_name; node_name | id | name | country | xc_node_id -------------------+----+---------+--------+------------- dn_6001_6002_6003 | 1 | Scott | China | -1072999043 dn_6004_6005_6006 | 2 | Henry | USA | -564789568 dn_6007_6008_6009 | 3 | Michael | France | 1532339558 dn_6007_6008_6009 | 4 | Jack | UK | 1532339558 (4 rows)--删除表。 gaussdb=# DROP TABLE test_list;
相关链接
优化建议
- UNLOGGED
- UNLOGGED表和表上的索引因为数据写入时不通过WAL日志机制，写入速度远高于普通表。因此，可以用于缓冲存储复杂查询的中间结果集，增强复杂查询的性能。
- UNLOGGED表无主备机制，在系统故障或异常断点等情况下，会有数据丢失风险，因此，不可用来存储基础数据。
- TEMPORARY
- 临时表只在当前会话可见，会话结束后会自动删除。
- LIKE
- 新表自动从这个表中继承所有字段名及其数据类型和非空约束，新表与源表之间在创建动作完毕之后是完全无关的。
- ORIENTATION ROW
- 创建行存表，行存储适合于OLTP业务，此类型的表上交互事务比较多，一次交互会涉及表中的多个列，用行存查询效率较高。
- DISTRIBUTE BY
- 事实表或者数据量较大的维度表建议创建为分布表。对指定的列进行Hash，通过映射，把数据分布到指定DN。语法为:DISTRIBUTE BY HASH(column_name)。
- 数据量较小的维度表建议创建为复制表。表的每条记录存在所有数据节点（DN）中，即每个数据节点都有完整的表数据。语法为: DISTRIBUTE BY REPLICATION。
