CREATE TABLE

功能描述

在当前数据库中创建一个新的空白表。

该表由命令执行者所有，但系统管理员在普通用户同名schema下创建的表，表的所有者为schema的同名用户（非系统管理员）。

注意事项

• 列存表支持的数据类型请参考列存表支持的数据类型。
• 创建列存和HDFS分区表的数量建议不超过1000个。
• 表中的主键约束和唯一约束必须包含分布列。
• 不支持修改已有表的分布列数据类型。
• 行存REPLICATION分布表不支持将系统列设置为主键。
• 如果在建表过程中数据库系统发生故障，系统恢复后可能无法自动清除之前已创建的、大小为0的磁盘文件。此种情况出现概率小，不影响数据库系统的正常运行。
• 列存表支持PARTIAL CLUSTER KEY、主键和唯一表级约束，不支持外键表级约束。
• 列存表的字段约束只支持NULL、NOT NULL和DEFAULT常量值。
• 列存表支持delta表，受表级参数enable_delta控制是否开启，受参数deltarow_threshold控制进入delta表的阈值。不推荐使用列存带Delta表，否则会出现由于来不及merge而导致的磁盘膨胀以及性能劣化等问题。
• 冷热表仅支持列存分区表，依赖于可用的OBS服务。
• 冷热表仅支持默认表空间为default_obs_tbs，如需新增obs表空间可联系技术支持。
• 如需创建列存表，需显式设置orientation属性为column，存算分离版本如需创建本地表（数据全部存储在EVS盘），需显式指定colversion=2.0。
• 创建表后，不支持通过ALTER TABLE语法将非V3表切换为V3表（即colversion为2.0不支持切为3.0）。
• V3表（即colversion=3.0，存算分离表）不支持设置delta表和列存二级分区。
• V3表不支持设置为Hstore表、冷热表、时序表。
• V3表不支持创建全局临时表和临时表，创建的临时表会自动转化为colversion=2.0的临时表。

• 不建议创建普通表时指定自定义TABLESPACE。
• 创建行存表时应避免指定COMPRESS压缩属性。
• 创建HASH分布的表对象时，要确保数据分布均匀（10G以上数据量的表，倾斜率控制在10%以内）。
• 创建REPLICATION分布的表对象，要确保表数据量控制在100万行以内。
• 创建HSTORE表时，必须确保数据库GUC参数设置满足以下条件：
  • autovacuum设置为on。
  • autovacuum_max_workers_hstore取值大于0。
  • autovacuum_max_workers取值大于autovacuum_max_workers_hstore的取值。
• 针对存在时间字段的大表（数据量5000万行以上），必须设计成分区表，根据查询特征合理设计分区间隔。
• 针对有大批量数据增删改的表，索引个数建议控制在3个以内，最多不超过5个。
• 更多开发设计规范参见总体开发设计规范。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE [ [ GLOBAL | LOCAL | VOLATILE ] { 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 [...] ] }
        [, ... ])|
        LIKE source_table [ like_option [...] ] } 
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
    [ COMPRESS | NOCOMPRESS ]
    [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
    [ COMMENT [=] 'text' ];

• 其中列约束column_constraint为：

  1 2 3 4 5 6 7 8 9 10 11

  [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | ON UPDATE on_update_expr | COMMENT 'text' | UNIQUE [ NULLS [NOT] DISTINCT | NULLS IGNORE ] index_parameters | PRIMARY KEY index_parameters } REFERENCES reftable [ ( refcolumn ) ] } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

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

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

• 其中表约束table_constraint为：

  1 2 3 4 5 6

  [ CONSTRAINT constraint_name ] { CHECK ( expression ) | UNIQUE [ NULLS [NOT] DISTINCT | NULLS IGNORE ] ( 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 | DROPCOLUMNS | ALL }

• 其中索引参数index_parameters为：

  1	[ WITH ( {storage_parameter = value} [, ... ] ) ]

表设计参考

GaussDB(DWS)兼容PostgreSQL生态，行存及其btree索引和PostgreSQL类似，列存及其索引为自研。建表时，选择合适的存储方式、分布列、分区键、索引，能够使SQL在执行时快速命中数据，减少IO消耗。下图是一个SQL从发起到获取数据的执行流程，通过下图，可以理解每个技术手段的作用，用于指导性能调优。

参数说明

• UNLOGGED

  如果指定此关键字，则创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中，这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动截断，会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。

  使用场景：非日志表不能保证数据的安全性，用户应该在确保数据已经做好备份的前提下使用，例如系统升级时进行数据的备份。

  故障处理：当异常关机等操作导致非日志表上的索引发生数据丢失时，用户应该对发生错误的索引进行重建。

  

  1. UNLOGGED表无主备机制，在系统故障或异常断点等情况下，会有数据丢失风险，不可用来存储基础数据。
  2. 9.1.0版本，UNLOGGED表默认存储在pg_unlogged表空间下，且不可迁移或指定到其他表空间。
  3. 从低版本升级到9.1.0版本后，旧版本创建的UNLOGGED表还存储在原表空间下。
  4. 由于实例异常重启时会对UNLOGGED表进行重置，所以可能影响实例RTO。9.1.0版本提供UNLOGGED表迁移脚本switch_unlogged_tablepace.py，配合GUC参数enable_unlogged_tablespace_compat，可以优化RTO。
• GLOBAL | LOCAL | VOLATILE

  创建临时表时在TEMP或TEMPORARY前指定GLOBAL、LOCAL、VOLATILE关键字，以创建不同属性的临时表。

  • 目前GLOBAL、LOCAL关键字的设立，仅是为了兼容SQL标准，无论指定GLOBAL还是LOCAL，GaussDB(DWS)都会创建LOCAL临时表。
  • 指定关键字VOLATILE，则创建VOLATILE临时表。
  • 当default_temptable_type=local时，不指定关键字创建的临时表为local类型临时表，当default_temptable_type=volatile时，不指定关键字创建的临时表为volatile类型临时表。
• TEMPORARY | TEMP

  如果指定TEMP或TEMPORARY关键字，则创建的表为临时表。临时表只在当前会话可见，本会话结束后会自动删除。因此，在除当前会话连接的CN以外的其他CN故障时，仍然可以在当前会话上创建和使用临时表。由于临时表只在当前会话创建，对于涉及对临时表操作的DDL语句，会产生DDL失败的报错。因此，建议DDL语句中不要对临时表进行操作。TEMP和TEMPORARY等价。

  

  • LOCAL/VOLATILE临时表通过每个会话独立的以pg_temp开头的schema来保证只对当前会话可见，因此，不建议用户在日常操作中手动删除以pg_temp，pg_toast_temp开头的schema。
  • 如果建表时不指定TEMPORARY/TEMP关键字，而指定表的schema为当前会话的pg_temp_开头的schema，则此表会被创建为临时表。
  • LOCAL临时表的所有相关元数据同普通表类似，都存储在系统表内，而VOLATILE临时表会将除schema外的相关表结构元数据直接存储在内存中。所以相对本地临时表而言，VOLATILE临时表有更多约束：
    • 当前CN或DN重启之后，对应实例上的内存数据丢失，相关volatile临时表会失效。
    • VOLATILE临时表当前不支持ALTER/GRANT等修改表结构相关操作。
    • VOLATILE临时表和LOCAL临时表共用一种临时schema，所以在同一session中，VOLATILE临时表和LOCAL临时表不能存在同名表。
    • VOLATILE临时表信息不存储在系统表内，所以无法通过对系统表执行DML语句查询到VOLATILE相关元数据。
    • VOLATILE临时表仅支持普通的行存、列存表，不支持delta表、时序表、冷热表。
    • 不支持基于VOLATILE临时表创建视图。
    • 不支持创建临时表时指定tablespace(VOLATILE临时表默认tablespace均为pg_volatile)。
    • 创建VOLATILE临时表时不支持指定约束：CHECK约束、UNIQUE约束、主键约束、触发器约束、EXCLUDE约束、PARTIAL CLUSTER约束。
  • GLOBAL临时表的所有相关元数据同普通表类似，都存储在系统表内。
    • GLOBAL临时表与LOCAL临时表不同的是，会话退出时元数据不会删除，但会话的数据会删除。不同会话的数据独立，但共享同一份GLOBAL临时表的元数据。
    • 全局临时表的schema与普通表类似，不是以pg_temp开头的schema，但与LOCAL/VOLATILE临时表不同，所以可以与LOCAL/VOLATILE临时表同名。
    • 全局临时表仅支持普通的行存、列存表，不支持delta表、时序表、冷热表。
    • 不支持操作其他逻辑集群的全局临时表。
• IF NOT EXISTS

  指定IF NOT EXISTS时，若不存在同名表，则可以成功创建表。若已存在同名表，创建时不会报错，仅会提示该表已存在并跳过创建。

• table_name

  要创建的表名。

  表名长度不超过63个字符，以字母或下划线开头，可包含字母、数字、下划线、$、#。

  使用双引号括起来的表名可以包含空格和特殊字符，但不建议在表名中使用这些字符，因为这样可能会使表名难以引用和使用，而且不同的数据库兼容模式下可能对于这种表名的处理方式也有所不同。

• column_name

  新表中要创建的字段名。

  字段名长度不超过63个字符，以字母或下划线开头，可包含字母、数字、下划线、$、#。

• data_type

  字段的数据类型。

  

  在兼容Teradata或MySQL语法的数据库中，字段数据类型指定为DATE时同样返回为DATE类型，否则返回TIMESTAMP类型。

• compress_mode

  表字段的压缩选项，当前仅对行存表有效。该选项指定表字段优先使用的压缩算法。

  取值范围：DELTA、PREFIX、DICTIONARY、NUMSTR、NOCOMPRESS

• COLLATE collation

  COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。

• LIKE source_table [ like_option ... ]

  LIKE子句声明一个表，新表自动从这个表中继承所有字段名及其数据类型和非空约束。

  新表与源表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会传播到新表中，并且也不可能在扫描源表的时候包含新表的数据。

  被复制的列和约束并不使用相同的名字进行融合。如果明确的指定了相同的名字或者在另外一个LIKE子句中，将会报错。

  • 源表上的字段缺省表达式或者ON UPDATE表达式只有在指定INCLUDING DEFAULTS时，才会复制到新表中。缺省是不包含缺省表达式的，即新表中的所有字段的缺省值都是NULL。
  • 源表上的CHECK约束仅在指定INCLUDING CONSTRAINTS时，会复制到新表中，而其他类型的约束永远不会复制到新表中。非空约束总是复制到新表中。此规则同时适用于表约束和列约束。
  • 如果指定了INCLUDING INDEXES，则源表上的索引也将在新表上创建，默认不建立索引。
  • 如果指定了INCLUDING STORAGE，则复制列的STORAGE设置会复制到新表中，默认情况下不包含STORAGE设置。
  • 如果指定了INCLUDING COMMENTS，则源表列、约束和索引的注释会复制到新表中。默认情况下，不复制源表的注释。
  • 如果指定了INCLUDING PARTITION，则源表的分区定义会复制到新表中，同时新表将不能再使用PARTITION BY子句。默认情况下，不拷贝源表的分区定义。
  • 如果指定了INCLUDING RELOPTIONS，则源表的存储参数（即源表的WITH子句）会复制到新表中。默认情况下，不复制源表的存储参数。
    

    WITH中的'PERIOD'，'TTL'为partition相关参数，LIKE INCLUDING RELOPTIONS不会复制到新表中，若要复制需INCLUDING PARTITION。

  • 如果指定了INCLUDING DISTRIBUTION，则源表的分布信息会复制到新表中，包括分布类型和分布列，同时新表将不能再使用DISTRIBUTE BY子句。默认情况下，不拷贝源表的分布信息。
  • 如果指定了INCLUDING DROPCOLUMNS，则源表被删除的列信息会被复制到新表中。默认情况下，不复制源表的删除列信息。
  • INCLUDING ALL包含了INCLUDING DEFAULTS、INCLUDING CONSTRAINTS、INCLUDING INDEXES、INCLUDING STORAGE、INCLUDING COMMENTS、INCLUDING PARTITION、INCLUDING RELOPTIONS、INCLUDING DISTRIBUTION和INCLUDING DROPCOLUMNS的内容。
  • 如果指定了EXCLUDING，则表示不包括指定的参数。
  • 如果是OBS冷热表，INCLUDING PARTITION后新表所有分区均为本地热分区。
  

  • 如果源表包含serial、bigserial、smallserial类型，或者源表字段的默认值是sequence，且sequence属于源表（通过CREATE SEQUENCE ... OWNED BY创建），这些Sequence不会关联到新表中，新表中会重新创建属于自己的sequence。这和之前版本的处理逻辑不同。如果用户希望源表和新表共享Sequence，需要首先创建一个共享的Sequence（避免使用OWNED BY），并配置为源表字段默认值，这样创建的新表会和源表共享该Sequence。
  • 不建议将其他表私有的Sequence配置为源表字段的默认值，尤其是其他表只分布在特定的NodeGroup上，这可能导致CREATE TABLE ... LIKE执行失败。另外，如果源表配置其他表私有的Sequence，当该表删除时Sequence也会连带删除，这样源表的Sequence将不可用。如果用户希望多个表共享Sequence，建议创建共享的Sequence。
• WITH ( { storage_parameter = value } [, ... ] )

  这个子句为表或索引指定一个可选的存储参数。

  

  使用任意精度类型Numeric定义列时，建议指定精度p以及刻度s。在不指定精度和刻度时，会按输入的显示出来。

  参数的详细描述如下所示。

  • FILLFACTOR

    一个表的填充因子（fillfactor）是一个介于10和100之间的百分数。100（完全填充）是默认值。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是合适的选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。该参数对于列存表没有意义。

    取值范围：10~100

  • ORIENTATION

    指定表数据的存储方式，即行存方式、列存方式，该参数设置成功后就不再支持修改。

    取值范围：

    • ROW，表示表的数据将以行式存储。

      行存储适合于OLTP业务，此类型的表上交互事务比较多，一次交互会涉及表中的多个列，用行存查询效率较高。

    • COLUMN，表示表的数据将以列式存储。

      列存储适合于数据仓库业务，此类型的表上会做大量的汇聚计算，且涉及的列操作较少。

    默认值：ROW，即行存方式。

  • COMPRESSION

    指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。

    取值范围：

    列存表的有效值为YES/NO和/LOW/MIDDLE/HIGH，默认值为LOW。当设置为YES时，压缩级别默认为LOW。

    

    • 暂不支持行存表压缩功能。
    • ORC格式迁移到GaussDB(DWS)列存表，如果使用low级别压缩，单副本大小大约是ORC的1.5~2倍；如果使用high级别压缩，单副本大小基本与ORC持平，在新建GaussDB(DWS)集群规模时，需考虑该转换关系。
    • 列存middle压缩固定使用字典压缩，对于数据特征不适合字典压缩的数据，使用middle压缩的结果可能比low压缩更大。

    GaussDB(DWS)内部提供如下压缩算法。

    表2 列存压缩算法

    COMPRESSION	NUMERIC	STRING	INT
    LOW	delta压缩+RLE压缩	lz4压缩	delta压缩（RLE可选）
    MIDDLE	delta压缩+RLE压缩+lz4压缩	dict压缩或lz4压缩	delta压缩或lz4压缩（RLE可选）
    HIGH	delta压缩+RLE压缩+zlib压缩	dict压缩或zlib压缩	delta压缩或zlib压缩（RLE可选）

  • COMPRESSLEVEL

    指定表数据同一压缩级别下的不同压缩水平，它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分，为用户选择压缩比和压缩时间提供了更多的空间。总体来讲，此值越大，表示同一压缩级别下压缩比越大，压缩时间越长；反之亦然。该参数只对列存表有效。

    取值范围：0~3

    默认值：0

  • TTL

    设置分区表定时删除分区task任务。默认不创建删除分区task任务。

    取值范围：1 hour ~ 100 years

  • PERIOD

    设置分区表定时创建分区task任务。如果设置TTL，PERIOD不能大于TTL。

    取值范围：1 hour ~ 100 years

    默认值：1 day

  • MAX_BATCHROW

    指定了在数据加载过程中一个存储单元可以容纳记录的最大数目。该参数只对列存表有效。

    取值范围：10000~60000

    默认值：60,000

  • PARTIAL_CLUSTER_ROWS

    指定了在数据加载过程中进行将局部聚簇存储的记录数目。该参数只对列存表有效。

    取值范围：600000~2147483647

    默认值：4,200,000

  • time_format

    自增减分区支持INT4/INT8/VARCHAR/TEXT列，存储内容为时间时使用。time_format选项仅当分区键为INT4/INT8/VARCHAR/TEXT时，同时指定period才生效。该参数仅9.1.0.200及以上集群版本支持。

    1. time_format写法遵从postgresql规范，例如time_format = 'yyyymmdd'。
    2. 分区键的类型

       VARCHAR/TEXT对time_format的规格有如下限制：

       • 精度支持到秒级。
       • 输入内容不包括字母类型，例如month, am, pm等。
       • 时间必须从大到小排列，例如年月日小时分钟秒。

       INT4/INT8对time_format的规格有如下限制：

       • 精度支持到小时级。
       • 输入内容仅包括Y,M,D,HH24。
       • 时间必须从大到小排列，例如年月日小时。
    3. ALTER相关限制
       • 不支持set。
       • 当period被reset时（表示已经关闭自动分区，会报出提示），此时可以reset此选项。
  • enable_delta

    指定了列存表是否开启delta表。该参数只对列存表有效。V3表暂不支持该参数，即COLVERSION为3.0时不支持设置enable_delta为on。

    不推荐使用列存带Delta表，否则会出现由于来不及merge而导致的磁盘膨胀以及性能劣化等问题。

    默认值：off

  • enable_hstore

    指定了是否创建为HStore表（基于列存表实现）。该参数只对列存表有效。该参数仅8.2.0.100及以上集群版本支持。V3表暂不支持该参数，即COLVERSION为3.0时不支持设置enable_delta为on。

    默认值：off

    

    打开该参数时必须设置以下GUC参数用于保证HStore表的清理，推荐值如下：

    autovacuum=on，autovacuum_max_workers=6，autovacuum_max_workers_hstore=3。

  • enable_disaster_cstore

    指定了列存表是否开启细粒度容灾功能。该参数仅适用于COLVERSION为2.0的列存表，并且不能和enable_hstore同时打开。该参数仅8.2.0.100及以上集群版本支持。

  • fine_disaster_table_role

    该参数8.2.1版本中已废弃，为兼容历史版本功能保留该参数，当前版本设置无效。

    指定了细粒度容灾表的表角色为主表还是备表。使用该参数时，必须要保证已经开启了enable_disaster_cstore参数。

    取值范围：

    • primary，表示细粒度容灾主表。
    • standby，表示细粒度容灾备表。
  • DELTAROW_THRESHOLD

    指定列存表导入时小于多少行的数据进入delta表，只在表级参数enable_delta开启时生效。该参数只对列存表有效。

    取值范围：0～60000

    默认值：6000

  • COLVERSION

    指定列存存储格式的版本，支持不同存储格式版本之间的切换。

    取值范围：

    1.0：列存表的每列以一个单独的文件进行存储，文件名以relfilenode.C1.0、relfilenode.C2.0、relfilenode.C3.0等命名。

    2.0：列存表的每列合并存储在一个文件中，文件名以relfilenode.C1.0命名。

    3.0：列存表的每列合并存储在一个文件中，文件存储在OBS文件系统上，文件名以C1_fileid.0命名。

    默认值：存算一体版本默认值2.0，存算分离版本默认值3.0。

    需注意，OBS冷热表仅支持COLVERSION为2.0格式。

    

    • 8.1.0集群版本该参数默认值为1.0，8.1.1及以上集群版本该参数默认值为2.0，若集群版本由8.1.0升级至8.1.1或以上版本，该参数默认值也会由1.0变为2.0。
    • 在建列存表时，选择COLVERSION=2.0，相比于1.0存储格式，在以下场景中性能有明显提升：
      • 创建列存宽表场景下，建表时间显著减少。
      • roach备份数据场景下，备份时间显著减少。
      • build、catch up耗时显著减少。
      • 占用磁盘空间大小显著减少。
    • 存算分离版本兼容列存所有版本，建表时需显式指定colversion=1.0/2.0/3.0。当colversion=3.0时建立表为存算分离表，不显式指定colversion时默认创建3.0版本列存表。需注意，创建存算分离表时指定colversion为3.0的同时需要将orientation属性设置为column。
    • V3存算分离表不支持使用ALTER TABLE切换colversion，比如从2.0升级到3.0。
  • analyze_mode

    控制表级自动analyze的方式。

    取值范围：

    • frozen：禁止所有形式的analyze（例外：无统计信息时，还能触发动态采样）。
    • backend：仅允许autovacuum轮询触发的analyze。
    • runtime：仅允许优化器触发的runtime analyze。
    • all：backend和runtime两种自动analyze都允许触发。

    默认值：all

  • incremental_analyze

    控制分区表是否启用增量analyze模式。该参数只对分区表有效，复制表不支持设置。该参数仅9.1.0.100及以上集群版本支持。

    默认值：false

  • SKIP_FPI_HINT

    顺序扫描过程中，若需要写FPW(full page writes)日志时，该参数控制是否跳过设置HintBits操作。

    默认值：false

    

    设置SKIP_FPI_HINT=true时，在对某表执行checkpoint操作后，若对该表进行顺序扫描，将不再产生Xlog。适用于查询次数较少的中间表，有效减少Xlog的大小，提升查询性能。

  • enable_column_autovacuum_garbage

    用于控制是否开启列存表AUTOVACUUM重写CU的逻辑。该参数仅8.2.1.100及以上集群版本支持。

    当前列存轻量化UPDATE与后台列存AUTOVACUUM并发会小概率报错， 可以通过设置该表级参数为off来避免。

    默认值：true

  • secondary_part_column

    指定列存表二级分区列的列名，仅能指定一列作为二级分区列且只适用于HStore列存表。该参数仅8.3.0及以上集群版本支持。

    

    • 被指定为二级分区的列不能被删除或者修改。
    • 只能在建表时指定二级分区列，建表后不支持修改。
    • 不建议指定分布列作为二级分区列。
    • 指定二级分区列后，该表将会在DN上按照二级分区列进行逻辑上的HASH分区存储，从而有效提升该列等值查询的性能。
  • secondary_part_num

    指定列存表二级分区的数量，仅适用于HStore列存表。该参数仅8.3.0及以上集群版本支持。

    取值范围：1~32

    默认值：8

    

    • 只有secondary_part_column被指定时，该参数才可以指定。
    • 只能在建表时指定二级分区的数量，建表后不支持修改。
    • 不建议用户随意设置该默认值，否则会影响导入和查询的性能。
  • enable_hstore_opt

    enable_hstore_opt表级参数打开时会默认同时打开enable_hstore表级参数，该参数仅8.3.0及以上集群版本支持。该参数支持v2版本。

    默认值：false

  • bitmap_columns

    bitmap index只适用于新版hstore（hstore_opt表），只有开启表级参数enable_hstore_opt且开启bitmap_columns="指定列"，才能生成bitmap index映射关系。该参数仅8.3.0及以上集群版本支持。

  • enable_turbo_store

    用于控制是否创建为turbo表（基于列存表实现）。该参数只对列存表有效。

    默认值：off

    

    turbo表对部分numeric和varchar数据类型做了整数格式存储优化，以提升对于numeric和varchar数据类型的处理速度。

    • 为获取最佳性能，推荐定义numeric类型时指定precision和scale(p,s)。具体来说，当p小于等于9时，使用4字节整数存储；p小于等于18时，使用8字节整数存储；p小于等于37时，使用16字节整数存储；p大于37或未指定时，则依然使用变长格式存储，其占用较多存储空间，且性能较差。
    • 如果定义varchar类型时指定其最大字节长度n小于等于2字节，使用2字节整数存储；n小于等于4字节时，使用4字节整数存储；依次类推，最大当n小于等于16字节时，使用16字节整数存储。因此对于varchar类型的GROUP BY列或者HashJoin关联列，如果可使用整数存储字符串，建议定义时指定其最大字节长度n小于等于16，可以成倍提升计算性能。
    • turbo表当前不支持时序表、delta表以及轻量化update。
  • cache_policy（该参数仅9.0.2及以上的存算分离版本支持）

    控制了表或者分区表（磁盘）缓存的方式，如果在缓存策略中指定相应参数范围，则会进行热缓存，否则进行冷缓存。热缓存相比冷缓存占用的空间更大，技术上使用更加复杂的替换策略。

    取值范围：

    • ALL： 对整个表进行热缓存。
    • NONE：对整个表进行冷缓存。
    • HPN：N分区表中前N个分区会被热缓存，其余分区进行冷缓存。
    • HPL：P1, P2, ... 分区表中在缓存策略中被指定名称的分区会被热缓存，其余分区进行冷缓存。

    默认值：ALL

    

    • 对于外表和非分区内表只支持ALL和NONE两种缓存策略。
    • 仅range和list分区内表支持HPN和HPL缓存策略。
• ON COMMIT { PRESERVE ROWS | DELETE ROWS }

  ON COMMIT选项决定在事务中执行创建临时表操作，当事务提交时，此临时表的后续操作。

  • PRESERVE ROWS（缺省值）：提交时不对临时表做任何操作，临时表及其表数据保持不变。
  • DELETE ROWS：提交时删除临时表中数据。
• COMPRESS | NOCOMPRESS

  创建新表时，需要在CREATE TABLE语句中指定关键字COMPRESS，这样，当对该表进行批量插入时就会触发压缩特性。该特性会在页范围内扫描所有元组数据，生成字典、压缩元组数据并进行存储。指定关键字NOCOMPRESS则不对表进行压缩。

  缺省值：NOCOMPRESS，即不对元组数据进行压缩。

• DISTRIBUTE BY

  指定表如何在节点之间分布或者复制。

  取值范围：

  • REPLICATION：表的每一行存在所有数据节点（DN）中，即每个数据节点都有完整的表数据。
  • ROUNDROBIN：表的每一行被轮番地发送给各个DN，因此数据会被均匀地分布在各个DN中。（ROUNDROBIN仅8.1.2及以上版本支持）
  • HASH (column_name ) ：对指定的列进行Hash，通过映射，把数据分布到指定DN。
    

    • 当指定DISTRIBUTE BY HASH (column_name)参数时，创建主键和唯一索引必须包含“ column_name”列。
    • 当被参照表指定DISTRIBUTE BY HASH (column_name)参数时，参照表的外键必须包含“ column_name”列。
    • 如果TO GROUP指定为复制表节点组（8.1.2及以上版本支持），DISTRIBUTE BY必须指定为REPLICATION。如果没有指定DISTRIBUTE BY，创建的表会自动设置为复制表。
    • 实时数仓（单机部署）由于只有单DN，因此分布规则会被忽略，也不支持针对分布规则的修改。
  默认值：由GUC参数default_distribution_mode控制。

  • 当default_distribution_mode=roundrobin时，DISTRIBUTE BY的默认值按如下规则选取：
    1. 若建表时包含主键/唯一约束，则选取HASH分布，分布列为主键/唯一约束对应的列。
    2. 若建表时不包含主键/唯一约束，则选取ROUNDROBIN分布。
  • 当default_distribution_mode=hash时，DISTRIBUTE BY的默认值按如下规则选取：
    1. 若建表时包含主键/唯一约束，则选取HASH分布，分布列为主键/唯一约束对应的列。
    2. 若建表时不包含主键/唯一约束，但存在数据类型支持作分布列的列，则选取HASH分布，分布列为第一个数据类型支持作分布列的列。
    3. 若建表时不包含主键/唯一约束，也不存在数据类型支持作分布列的列，选取ROUNDROBIN分布。
  以下数据类型支持作为分布列：

  • INTEGER TYPES：TINYINT，SMALLINT，INT，BIGINT，NUMERIC/DECIMAL
  • CHARACTER TYPES：CHAR，BPCHAR，VARCHAR，VARCHAR2，NVARCHAR2，TEXT
  • DATE/TIME TYPES：DATE，TIME，TIMETZ，TIMESTAMP，TIMESTAMPTZ，INTERVAL，SMALLDATETIME
  

  在建表时，选择分布列和分区键可对SQL查询性能产生重大影响。因此，需要根据一定策略选择合适的分布列和分区键。

  • 选择合适的分布列
    对于采用散列（Hash）方式的数据分布表，一个合适的分布列应将一个表内的数据，均匀分散存储在多个DN内，避免出现数据倾斜现象（即多个DN内数据分布不均）。请按照如下原则判定合适的分布列：

    1. 判断是否已发生数据倾斜现象。

       连接数据库，执行如下语句，查看各DN内元组数目。命令中的斜体部分tablename，请填入待分析的表名。

       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内元组数目相差较大（如相差数倍、数十倍），则表明已发生数据倾斜现象，请按照下面原则调整分布列。

    2. 重新选择分布列，可通过ALTER TABLE语句调整分布列，选择原则如下：

       分布列的列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。

       在满足上面原则的情况下，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。

    3. 如果找不到一个合适的分布列，使数据能够均匀分布到各个DN，那么可以考虑使用REPLICATION或ROUNDROBIN的数据分布方式。由于REPLICATION的数据分布方式会在每个DN中存放完整的数据，因此在表较大且找不到合适的分布列时，推荐使用ROUNDROBIN的数据分布方式。（ROUNDROBIN分布方式8.1.2及以上版本支持）
  • 选择合适的分区键

    数据分区功能，可根据表的一列或者多列，将要插入表的记录分为若干个范围（这些范围在不同的分区里没有重叠）。然后为每个范围创建一个分区，用来存储相应的数据。

    调整分区键，使每次查询结果尽可能存储在相同或者最少的分区内（称为“分区剪枝”），通过获取连续I/O大幅度提升查询性能。

    实际业务中，经常将时间作为查询对象的过滤条件，因此，可考虑选择时间列为分区键，键值范围可根据总数据量、一次查询数据量调整。

• TO { GROUP groupname | NODE ( nodename [, ... ] ) }

  TO GROUP指定创建表所在的Node Group，目前不支持hdfs表使用。TO NODE主要供内部扩容工具使用，一般用户不应该使用。

  在逻辑集群模式下，如果不指定TO GROUP，表默认会创建在逻辑集群用户关联的节点组中；如果用户没有管理逻辑集群（例如管理员用户或其他普通用户），表默认会创建在第一个逻辑集群中（pgxc_group中oid最小的逻辑集群是第一个逻辑集群）。

  如果TO GROUP指定的节点组是复制表节点组，表将创建在所有CN和DN节点上，但复制表数据将只分布在复制表节点组包含的DN节点上。

  存算分离3.0版本支持只读逻辑集群，如果未绑定到只读逻辑集群的用户建表语句中TO GROUP指定的是只读逻辑集群，则创建表会报错；但绑定到只读逻辑集群的用户创建表会遵循未绑定逻辑集群用户的建表规则，将表创建到由GUC参数default_storage_nodegroup指定的逻辑集群上。如果default_storage_nodegroup是installation, 表会创建在第一个逻辑集群中。

• COMMENT [=] 'text'

  COMMENT子句可在创建表时指定表注释。

• CONSTRAINT constraint_name

  列约束或表约束的名字。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。

  定义约束有两种方法：

  • 列约束：作为一个列定义的一部分，仅影响该列。
  • 表约束：不和某个列绑在一起，可以作用于多个列。
• NOT NULL

  字段值不允许为NULL。

• NULL

  字段值允许为NULL ，这是缺省值。

  这个子句只是为和非标准SQL数据库兼容。不建议使用。

• CHECK ( expression )

  CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。

  声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。

  

  expression表达式中，如果存在“<>NULL”或“！=NULL”，这种写法是无效的，需要写成“is NOT NULL”。

• DEFAULT default_expr

  DEFAULT子句给字段指定缺省值。该数值可以是任何不含变量的表达式(不允许使用子查询和对本表中的其他字段的交叉引用)。缺省表达式的数据类型必须和字段类型匹配。

  缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。

• ON UPDATE on_update_expr

  ON UPDATE子句给字段指定时间戳函数。ON UPDATE子句支持指定的字段类型必须为timestamp类型或者timestamptz类型。

  当执行含有update操作的SQL语句时，自动更新此列为时间戳函数所代表的时间。

  

  on_update_expr时间戳函数仅支持CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE, LOCALTIME, LOCALTIMESTAMP。

• COMMENT 'text'

  COMMENT子句可以指定列的注释。

• UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] index_parameters

  UNIQUE [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] ( column_name [, ... ] ) index_parameters

  UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。

  其中[ NULLS [ NOT ] DISTINCT | NULLS IGNORE ]字段用来指定Unique唯一索引中索引列NULL值的处理方式。

  默认值：该参数默认缺省，即NULL值可重复插入。

  在对插入的新数据和表中原始数据进行列的等值比较时，对于NULL值有以下三种处理方式：

  • NULLS DISTINCT：NULL值互不相等，即NULL值可重复插入。
  • NULLS NOT DISTINCT：NULL值相等。若索引列全为NULL，则NULL值不可重复插入；部分索引列为NULL，只有非NULL值不相等，才可成功插入数据。
  • NULLS IGNORE：在等值比较时跳过NULL值。若索引列全为NULL，则NULL值可重复插入；部分索引列为NULL，只有非NULL值不相等，才可成功插入数据。

  三种处理方式具体的行为如下表所示：

  表3 唯一索引中索引列NULL值的处理方式

  字段控制	索引列全为NULL	部分索引列为NULL
  NULLS DISTINCT	可重复插入	可重复插入
  NULLS NOT DISTINCT	不可重复插入	非NULL值相等，不可插入；非NULL值不相等，则插入成功
  NULLS IGNORE	可重复插入	非NULL值相等，不可插入；非NULL值不相等，则插入成功

  

  如果没有声明DISTRIBUTE BY REPLICATION，则唯一约束的列集合中必须包含分布列。

• PRIMARY KEY index_parameters

  PRIMARY KEY ( column_name [, ... ] ) index_parameters

  主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。

  一个表只能声明一个主键。

  

  如果没有声明DISTRIBUTE BY REPLICATION，则主键约束的列集合中必须包含分布列。

• REFERENCES reftable [ ( refcolumn ) ]

  外键约束要求新表中一列或多列构成的组应该只包含、匹配被参考表中被参考字段值。被参考列应该是被参考表中的唯一字段或主键。

  

  由于GaussDB(DWS)不会进行外键约束检查，使用外键约束时需通过函数check_foreign_key_constraint来检测外键表中的数据是否满足外键约束。

• DEFERRABLE | NOT DEFERRABLE

  这两个关键字设置该约束是否可推迟。一个不可推迟的约束将在每条命令之后马上检查。可推迟约束可以推迟到事务结尾使用SET CONSTRAINTS命令检查。缺省是NOT DEFERRABLE。目前，只有行存的UNIQUE约束和主键约束可以接受这个子句。所有其他约束类型都是不可推迟的。

• PARTIAL CLUSTER KEY

  局部聚簇存储，列存表导入数据时按照指定的列(单列或多列)，进行局部排序。

• INITIALLY IMMEDIATE | INITIALLY DEFERRED

  如果约束是可推迟的，则这个子句声明检查约束的缺省时间。

  • 如果约束是INITIALLY IMMEDIATE（缺省），则在每条语句执行之后就立即检查它；
  • 如果约束是INITIALLY DEFERRED ，则只有在事务结尾才检查它。

  约束检查的时间可以用SET CONSTRAINTS命令修改。

示例

创建一个存算分离V3表（仅存算分离3.0版本支持）

1
2
3
4
5
6
7
8

CREATE TABLE  public.t1 
( 
id integer not null,  
data integer, 
age integer 
)  
WITH (ORIENTATION =COLUMN, COLVERSION =3.0) 
DISTRIBUTE BY ROUNDROBIN;

创建表的时候指定缓存策略（仅存算分离3.0版本集群支持）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE Sports
(
    N_NATIONKEY  INT NOT NULL
  , N_NAME       CHAR(25) NOT NULL
  , N_REGIONKEY  INT NOT NULL
  , N_COMMENT    VARCHAR(152)
) WITH (orientation = column, colversion = 3.0, cache_policy = 'HPL: Balls, Basketball')
tablespace cu_obs_tbs
DISTRIBUTE BY ROUNDROBIN
partition by list(N_NAME)
(
  partition Balls values ('Basketball', 'football', 'badminton'),
  partition Athletics values ('High jump', 'long jump', 'javelin'),
  partition Water_Sports values ('Surfing', 'diving', 'swimming'),
  partition Shooting values ('air guns', 'Rifles', 'archery'),
  partition rest values (DEFAULT)
);

为表定义唯一列约束：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT NOT NULL CONSTRAINT C_CUSTKEY_pk PRIMARY KEY  , 
    C_NAME        VARCHAR(25)  , 
    C_ADDRESS     VARCHAR(40)  , 
    C_NATIONKEY   INT          , 
    C_PHONE       CHAR(15)     , 
    C_ACCTBAL     DECIMAL(15,2)  
)
DISTRIBUTE BY HASH(C_CUSTKEY);

为表定义主键表约束。可以在表的一列或多列上定义主键表约束：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT       , 
    C_NAME        VARCHAR(25)  , 
    C_ADDRESS     VARCHAR(40)  , 
    C_NATIONKEY   INT          , 
    C_PHONE       CHAR(15)     , 
    C_ACCTBAL     DECIMAL(15,2)   , 
    CONSTRAINT C_CUSTKEY_KEY PRIMARY KEY(C_CUSTKEY,C_NAME)
)
DISTRIBUTE BY HASH(C_CUSTKEY,C_NAME);

定义CHECK列约束：

1
2
3
4
5
6
7
8

CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT NOT NULL CONSTRAINT C_CUSTKEY_pk PRIMARY KEY  , 
    C_NAME        VARCHAR(25)  , 
    C_ADDRESS     VARCHAR(40)  , 
    C_NATIONKEY   INT NOT NULL  CHECK (C_NATIONKEY > 0)  
)
DISTRIBUTE BY HASH(C_CUSTKEY);

定义CHECK表约束：

CREATE TABLE CUSTOMER
(    
    C_CUSTKEY     BIGINT NOT NULL CONSTRAINT C_CUSTKEY_pk PRIMARY KEY  , 
    C_NAME        VARCHAR(25)      , 
    C_ADDRESS     VARCHAR(40)      , 
    C_NATIONKEY   INT              , 
    CONSTRAINT C_CUSTKEY_KEY2 CHECK(C_CUSTKEY > 0 AND C_NAME <> '')
)
DISTRIBUTE BY HASH(C_CUSTKEY);

创建列存表指定存储格式和压缩方式：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE customer_address
(
    ca_address_sk       INTEGER                  NOT NULL   ,
    ca_address_id       CHARACTER(16)            NOT NULL   ,
    ca_street_number    CHARACTER(10)                       ,
    ca_street_name      CHARACTER varying(60)               ,
    ca_street_type      CHARACTER(15)                       ,
    ca_suite_number     CHARACTER(10)                    
)
WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH,COLVERSION=2.0)
DISTRIBUTE BY HASH (ca_address_sk);

使用DEFAULT为列W_STATE声明默认值：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE warehouse_t
(
    W_WAREHOUSE_SK            INTEGER                NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)               NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)   UNIQUE DEFERRABLE,
    W_WAREHOUSE_SQ_FT         INTEGER                        ,
    W_COUNTY                  VARCHAR(30)                    ,
    W_STATE                   CHAR(2)            DEFAULT 'GA',
    W_ZIP                     CHAR(10)                       
);

以like方式创建一个表CUSTOMER_bk ：

1

CREATE TABLE CUSTOMER_bk (LIKE CUSTOMER INCLUDING ALL);

相关链接

ALTER TABLE，12.101-RENAME TABLE，DROP TABLE