参数说明

• IF NOT EXISTS

  如果已经存在相同名称的表，不抛出错误，而是发出一个notice，告知表已存在。

• partition_table_name

  分区表的名称。

  取值范围：字符串，要符合标识符命名规范。

• column_name

  新表中要创建的字段名。

  取值范围：字符串，要符合标识符命名规范。

• data_type

  字段的数据类型。

• COLLATE collation

  COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“SELECT * FROM pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。

• CONSTRAINT constraint_name

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

  定义约束有两种方法：

  • 列约束：作为一个列定义的一部分，仅影响该列。
  • 表约束：不和某个列绑在一起，可以作用于多个列。在B模式数据库下（即sql_compatibility = 'B'）constraint_name为可选项，在其他模式数据库下，必须加上constraint_name。
• index_name

  索引名。

  

  • index_name仅在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库下不支持。
  • 对于外键约束，constraint_name和index_name同时指定时，索引名为constraint_name。
  • 对于唯一键约束，constraint_name和index_name同时指定时，索引名以index_name。
• USING method

  指定创建索引的方法。

  取值范围参考参数说明中的USING method。

  

  • USING method仅在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库下不支持。
  • 在B模式下，未指定USING method时，对于ASTORE的存储方式，默认索引方法为btree；对于USTORE的存储方式，默认索引方法为ubtree。
• ASC | DESC

  ASC表示指定按升序排序（默认）。DESC指定按降序排序。

  

  ASC|DESC只在B模式数据库下（即sql_compatibility = 'B'）支持，其他模式数据库不支持。

• LIKE source_table [ like_option ... ]

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

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

  • 字段缺省表达式只有在声明了INCLUDING DEFAULTS之后才会包含进来。缺省是不包含缺省表达式的，即新表中所有字段的缺省值都是NULL。
  • 如果指定了INCLUDING UPDATE，则原表列的ON UPDATE CURRENT_TIMESTAMP属性会复制到新表列中。默认不复制该属性。
  • 如果指定了INCLUDING GENERATED，则原表列的生成表达式会复制到新表中。默认不复制生成表达式。
  • 非空约束将总是复制到新表中，CHECK约束则仅在指定了INCLUDING CONSTRAINTS的时候才复制，而其他类型的约束则永远也不会被复制。此规则同时适用于表约束和列约束。

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

  • 如果指定了INCLUDING INDEXES，则原表上的索引也将在新表上创建，默认不建立索引。
  • 如果指定了INCLUDING STORAGE，则原表列的STORAGE设置也将被复制，默认情况下不包含STORAGE设置。
  • 如果指定了INCLUDING COMMENTS，则原表列、约束和索引的注释也会被复制过来。默认情况下，不复制源表的注释。
  • 如果指定了INCLUDING RELOPTIONS，则原表的存储参数（即源表的WITH子句）也将复制至新表。默认情况下，不复制源原的存储参数。
  • 如果指定了INCLUDING IDENTITY，则原表的identity功能会复制到新表中，并创建一个与原表SEQUENCE参数相同的SEQUENCE。默认情况下，不复制原表的identity功能。
  • 如果指定了INCLUDING ILM，则源表的ILM策略信息会被复制到新表中，如果需要同时复制源表上的分区对象的ILM策略信息，需要同时指定INCLUDING PARTITION。
  • INCLUDING ALL包含了INCLUDING DEFAULTS、INCLUDING UPDATE、INCLUDING CONSTRAINTS、INCLUDING INDEXES、INCLUDING STORAGE、INCLUDING COMMENTS、INCLUDING PARTITION、INCLUDING RELOPTIONS、INCLUDING IDENTITY和INCLUDING ILM的内容。
  

  • “CREATE TABLE table_name LIKE source_table;”语法仅在B模式数据库（即sql_compatibility = 'B'）下，且参数b_format_version值为5.7、b_format_dev_version值为s2时支持。
  • 在B模式数据库下，且参数b_format_version值为5.7、b_format_dev_version值为s2时，不支持指定INCLUDING和EXCLUDING选项，缺省等同于指定INCLUDING ALL。
• AUTO_INCREMENT [ = ] value

  这个子句为自动增长列指定一个初始值，value必须为正整数，不得超过2¹²⁷-1。

  

  该子句仅在参数sql_compatibility='B'时有效。

• COMMENT [ = ] 'string'
  • COMMENT [ = ] 'string'子句表示给表添加注释。
  • 在column_constraint中的COMMENT 'string'表示给列添加注释。
  • 在table_constraint中的COMMENT 'string'表示给主键和唯一键对应的索引添加注释。

  具体请参见：•COMMENT [ = ] 'string'。

• CHARACTER SET | CHARSET charset

  指定表字段的字符集。单独指定时会将字段的字符序设置为指定的字符集的默认字符序。

  仅在B模式数据库下（即sql_compatibility = 'B'）支持该语法，其他模式数据库不支持。

• COLLATE collation

  COLLATE子句指定列的排序规则（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“SELECT * FROM pg_collation”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。对于B模式数据库下（即sql_compatibility = 'B'）还支持utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci、binary、gbk_chinese_ci、gbk_bin、gb18030_chinese_ci、gb18030_bin字符序。

• WITH ( storage_parameter [= value] [, ... ] )

  这个子句为表或索引指定一个可选的存储参数。参数的详细描述如下所示：

  • FILLFACTOR

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

    取值范围：10~100

  • ORIENTATION

    决定了表的数据的存储方式。

    取值范围：

    ROW（缺省值）：表的数据将以行式存储。

    

    orientation不支持修改。

  • STORAGE_TYPE

    指定存储引擎类型，该参数设置成功后就不再支持修改。

    取值范围：

    • USTORE，表示表支持Inplace-Update存储引擎。特别需要注意，使用USTORE表，必须要开启track_counts和track_activities参数，否则会引起空间膨胀。
    • ASTORE，表示表支持Append-Only存储引擎。

    默认值：

    不指定时，由参数enable_default_ustore_table决定存储引擎方式，默认是Inplace-Update存储。

  • COMPRESSION
    • 该参数仅支持列存压缩。
  • segment

    使用段页式的方式存储。本参数仅支持行存表。不支持临时表、unlog表。

    取值范围：on/off

    默认值：off

  • statistic_granularity

    记录该表在分析统计信息时的默认partition_mode，partition_mode说明详见ANALYZE|ANALYSE参数说明，此参数对非分区表设置无效。

    取值范围：见partition_mode取值范围。

    默认值：AUTO。

  • enable_tde

    指定该表为加密表。数据库会自动将加密表中的数据先加密再存储。使用该参数前，请确保已通过GUC参数enable_tde开启透明加密功能，并通过GUC参数tde_key_info设置访问密钥服务的信息，在《特性指南》中“透明 数据加密 ”章节可获取该参数的详细使用方法。本参数仅支持行存表、段页式表、hashbucket表、临时表和unlogged表。

    取值范围：on/off。设置enable_tde=on时，key_type、tde_cmk_id、dek_cipher参数由数据库自动生成，用户无法手动指定或更改。

    默认值：off

  • encrypt_algo

    指定加密表的加密算法，需与enable_tde结合使用。

    取值范围：字符串，有效值为：AES_128_CTR，SM4_CTR。

    默认值：如果enable_tde=on，GUC参数tde_encrypt_config中子参数table_algorithm不为空，默认值与table_algorithm相同；如果table_algorithm为空，默认值为AES_128_CTR。如果enable_tde=off，默认值为空。

  • dek_cipher

    数据密钥的密文。用户为表设置enable_tde参数后，数据库自动生成数据密钥。

    取值范围：字符串

    默认值：空

  • key_type

    主密钥的类型。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的类型。

    取值范围：字符串

    默认值：空

  • cmk_id

    主密钥的ID。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的ID。

    取值范围：字符串

    默认值：空

  • autovacuum_enabled

    自动清理功能是否对该表启用。

    取值范围：on/off

    默认值：on

  • autovacuum_vacuum_threshold

    自动清理功能中，指定在该表中触发VACUUM所需的更新或删除的最小元组数（仅对Astore表生效）。

    取值范围：0-2147483647

    默认值：-1，缺省时与GUC参数autovacuum_vacuum_threshold一致。

  • autovacuum_analyze_threshold

    自动清理功能中，指定在该表中触发ANALYZE所需的插入、更新或删除的最小元组数。

    取值范围：0-2147483647

    默认值：-1，缺省时与GUC参数autovacuum_analyze_threshold一致。

  • autovacuum_vacuum_scale_factor

    自动清理功能中，指定在该表中触发VACUUM所需的插入、更新或删除元组的比例（仅对Astore表生效）。

    取值范围：0.0-100.0

    默认值：-1，缺省时与GUC参数autovacuum_vacuum_scale_factor一致。

  • autovacuum_analyze_scale_factor

    自动清理功能中，指定在该表中触发ANALYZE所需的插入、更新或删除元组的比例。

    取值范围：0.0-100.0

    默认值：-1，缺省时与GUC参数autovacuum_analyze_scale_factor一致。

  • autovacuum_freeze_min_age

    自动清理功能中，指定在该表参数指定了一个行版本的最小年龄，超过这个年龄的行才会被冻结。

    取值范围：0-1000000000

    默认值：-1，缺省时与GUC参数vacuum_freeze_min_age一致。

  • autovacuum_freeze_max_age

    自动清理功能中，该表pg_class.relfrozenxid字段在超过多少个事务后，就会强制执行VACUUM操作。即使自动清理被禁用，系统也会启动AUTOVACUUM进程。清理操作还允许从pg_clog/子目录中删除旧文件（仅对Astore表生效）。

    取值范围：100000-2000000000

    默认值：-1，缺省时与GUC参数autovacuum_freeze_max_age一致。

  • autovacuum_freeze_table_age

    自动清理功能中，该表被标记为不需要自动清理时，它将保持不变的时间。（仅对Astore表生效）。

    取值范围：0-2000000000

    默认值：-1，缺省时与GUC参数vacuum_freeze_table_age一致。

• [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]]

  创建新表时，可以调用ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW给行存添加高级压缩策略，分区继承表的策略。

  • AFTER n { day | month | year } OF NO MODIFICATION ：表示n天/月/年没有修改的行。
  • ON ( EXPR )：行级表达式，用于判断行的冷热。
• TABLESPACE tablespace_name

  指定新表将要在tablespace_name表空间内创建。如果没有声明，将使用默认表空间。

• PARTITION BY RANGE [COLUMNS] (partition_key)

  创建范围分区。partition_key为分区键的名称。

  COLUMNS关键字只能在sql_compatibility='B'时使用，“PARTITION BY RANGE COLUMNS” 语义同 “PARTITION BY RANGE”。

  （1）对于从句是VALUES LESS THAN的语法格式：

  

  对于从句是VALUES LESS THAN的语法格式，范围分区策略的分区键最多支持16列。

  该情形下，分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC、REAL、DOUBLE PRECISION、CHARACTER VARYING(n)、VARCHAR(n)、CHARACTER(n)、CHAR(n)、CHARACTER、CHAR、TEXT、NVARCHAR、NVARCHAR2、NAME、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。

  （2）对于从句是START END的语法格式：

  

  对于从句是START END的语法格式，范围分区策略的分区键仅支持1列。

  该情形下，分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC、REAL、DOUBLE PRECISION、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。

  （3）对于指定了INTERVAL子句的语法格式：

  

  对于指定了INTERVAL子句的语法格式，范围分区策略的分区键仅支持1列。

  该情形下，分区键支持的数据类型为：TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。

• PARTITION partition_name VALUES LESS THAN {( { partition_value | MAXVALUE } [,...] ) | MAXVALUE }

  指定各分区的信息。partition_name为范围分区的名称。partition_value为范围分区的上边界，取值依赖于partition_key的类型。MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。

  

  • 每个分区都需要指定一个上边界。
  • 分区上边界的类型应当和分区键的类型一致。
  • 分区列表是按照分区上边界升序排列的，值较小的分区位于值较大的分区之前。
  • 不在括号内的MAVALUE只能在sql_compatibility='B'时使用，并且只能有一个分区键。
• PARTITION partition_name {START (partition_value) END (partition_value) EVERY (interval_value)} | {START (partition_value) END (partition_value|MAXVALUE)} | {START(partition_value)} | {END (partition_value | MAXVALUE)}

  指定各分区的信息，各参数意义如下：

  • partition_name：范围分区的名称或名称前缀，除以下情形外（假定其中的partition_name是p1），均为分区的名称。
    • 若该定义是START+END+EVERY从句，则语义上定义的分区的名称依次为p1_1, p1_2, ...。例如对于定义“PARTITION p1 START(1) END(4) EVERY(1)”，则生成的分区是：[1, 2), [2, 3) 和 [3, 4)，名称依次为p1_1, p1_2和p1_3，即此处的p1是名称前缀。
    • 若该定义是第一个分区定义，且该定义有START值，则范围（MINVALUE, START）将自动作为第一个实际分区，其名称为p1_0，然后该定义语义描述的分区名称依次为p1_1, p1_2, ...。例如对于完整定义“PARTITION p1 START(1), PARTITION p2 START(2)”，则生成的分区是：(MINVALUE, 1), [1, 2) 和 [2, MAXVALUE)，其名称依次为p1_0, p1_1和p2，即此处p1是名称前缀，p2是分区名称。这里MINVALUE表示最小值。
  • partition_value：范围分区的端点值（起始或终点），取值依赖于partition_key的类型，不可是MAXVALUE。
  • interval_value：对[START，END) 表示的范围进行切分，interval_value是指定切分后每个分区的宽度，不可是MAXVALUE；如果（END-START）值不能整除以EVERY值，则仅最后一个分区的宽度小于EVERY值。
  • MAXVALUE：表示最大值，它通常用于设置最后一个范围分区的上边界。
  

  1. 在创建分区表若第一个分区定义含START值，则范围（MINVALUE，START）将自动作为实际的第一个分区。
  2. START END语法需要遵循以下限制：
     • 每个partition_start_end_item中的START值（如果有的话，下同）必须小于其END值；
     • 相邻的两个partition_start_end_item，第一个的END值必须等于第二个的START值；
     • 每个partition_start_end_item中的EVERY值必须是正向递增的，且必须小于（END-START）值；
     • 每个分区包含起始值，不包含终点值，即形如：[起始值，终点值)，起始值是MINVALUE时则不包含；
     • 一个partition_start_end_item创建的每个分区所属的TABLESPACE一样；
     • partition_name作为分区名称前缀时，其长度不要超过57字节，超过时自动截断；
     • 在创建、修改分区表时请注意分区表的分区总数不可超过最大限制（1048575）；
  3. 在创建分区表时START END与LESS THAN语法不可混合使用。
  4. 即使创建分区表时使用START END语法，备份（gs_dump）出的SQL语句也是VALUES LESS THAN语法格式。
• INTERVAL (interval_expr) [ STORE IN (tablespace_name [, ... ] ) ]

  间隔分区定义信息。

  • interval_expr：自动创建分区的间隔，需要符合partition_key的字段类型，目前只支持数值类型和日期/时间类型，例如：1 day、1 month。
  • STORE IN (tablespace_name [, ... ] )：指定存放自动创建分区的表空间列表，如果有指定，则自动创建的分区从表空间列表中循环选择使用，否则使用分区表默认的表空间。
• PARTITION BY LIST [COLUMNS] (partition_key)

  创建列表分区。partition_key为分区键的名称。

  COLUMNS关键字只能在sql_compatibility='B'时使用，“PARTITION BY LIST COLUMNS” 语义同 “PARTITION BY LIST”。

  • 对于partition_key，列表分区策略的分区键最多支持16列。
  • 对于从句是VALUES [IN] (list_values)的语法格式，list_values中包含了对应分区存在的键值，每个分区的键值数量不超过64个。
  • 从句"VALUES IN"只能在sql_compatibility='B'时使用，语义同"VALUES"。

  分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC、VARCHAR(n)、CHAR、BPCHAR、NVARCHAR、NVARCHAR2、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。分区个数不能超过1048575个。

• PARTITION BY HASH(partition_key)

  创建哈希分区。partition_key为分区键的名称。

  对于partition_key，哈希分区策略的分区键仅支持1列。

  分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC、VARCHAR(n)、CHAR、BPCHAR、TEXT、NVARCHAR、NVARCHAR2、TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE。分区个数不能超过1048575个。

• PARTITION BY KEY(partition_key)

  只能在sql_compatibility='B'时使用，语义同“PARTITION BY HASH(partition_key)”。

• AUTOMATIC

  创建新表时，若指定关键字AUTOMATIC，则开启列表分区的自动扩展功能，缺省时表示不开启自动扩展功能。只有列表分区可以使用自动扩展功能。

  开启自动扩展功能后，当插入数据无法匹配到已有分区时，会自动创建一个单独的分区。

• PARTITIONS integer

  指定分区个数。

  integer为分区数，必须为大于0的整数，且不得大于1048575。

  • 当在RANGE和LIST分区后指定此子句时，必须显式定义每个分区，且定义分区的数量必须与integer值相等。只能在sql_compatibility='B'时在RANGE和LIST分区后指定此子句。
  • 当在HASH和KEY分区后指定此子句时，若不列出各个分区定义，将自动生成integer个分区，自动生成的分区名为“p+数字”，数字依次为0到integer-1，分区的表空间默认为此表的表空间；也可以显式列出每个分区定义，此时定义分区的数量必须与integer值相等。若既不列出分区定义，也不指定分区数量，将创建唯一一个分区。
• { ENABLE | DISABLE } ROW MOVEMENT

  行迁移开关。

  如果进行UPDATE操作时，更新了元组在分区键上的值，造成了该元组所在分区发生变化，就会根据该开关给出报错信息，或者进行元组在分区间的转移。

  取值范围：

  • ENABLE（缺省值）：行迁移开关打开。
  • DISABLE：行迁移开关关闭。

  在打开行迁移开关情况下，并发UPDATE、DELETE操作可能会报错，原因如下：

  UPDATE和DELETE操作对于旧数据都是标记为已删除。在打开行迁移开关情况下，如果更新分区键时，导致了跨分区更新，内核会把旧分区中旧数据标记为已删除，在新分区中新增加一条数据，无法通过旧数据找到新数据。

  在UPDATE和UPDATE并发、DELETE和DELETE并发、UPDATE和DELETE并发三个并发场景下，如果并发操作同一行数据时，数据跨分区和非跨分区结果有不同的行为。

  1. 对于数据非跨分区结果，第一个操作执行完后，第二个操作不会报错。
     • 如果第一个操作是UPDATE，第二个操作能成功找到最新的数据，之后对新数据操作。
     • 如果第一个操作是DELETE，第二个操作看到当前数据已经被删除而且找不到最新数据，就终止操作。
  2. 对于数据跨分区结果，第一个操作执行完后，第二个操作会报错。
     • 如果第一个操作是UPDATE，由于新数据在新分区中，第二个操作不能成功找到最新的数据，就无法操作，之后会报错。
     • 如果第一个操作是DELETE，第二个操作看到当前数据已经被删除而且找不到最新数据，但无法判断删除旧数据的操作是UPDATE还是DELETE。如果是UPDATE，报错处理。如果是DELETE，终止操作。为了保持数据的正确性，只能报错处理。

  如果是UPDATE和UPDATE并发，UPDATE和DELETE并发场景，需要串行执行才能解决问题，如果是DELETE和DELETE并发，关闭行迁移开关可以解决问题。