CREATE TABLE PARTITION

功能描述

创建分区表。

分区表是指将逻辑上的一张表根据指定的方案分成几个物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际存储在分区上。

常见的分区方案有范围分区（Range Partitioning）、间隔分区（Interval Partitioning）、哈希分区（Hash Partitioning）、列表分区（List Partitioning）及数值分区（Value Partitioning）等。目前行存表支持范围分区、哈希分区以及列表分区。

• 范围分区：根据表的一列或者多列，将要插入表的记录分为若干个范围，这些范围在不同的分区里没有重叠。为每个范围创建一个分区，用来存储相应的数据。范围分区为最常用的分区策略。
• 哈希分区：根据表的一列，为每个分区指定模数和余数，将要插入表的记录划分到对应的分区中，每个分区所持有的行都需要满足条件（分区键的值除以为其指定的模数将产生为其指定的余数）。
• 列表分区：列表分区是根据表的一列，将要插入表的记录通过每一个分区中出现的键值划分到对应的分区中，这些键值在不同的分区里没有重叠。为每组键值创建一个分区，用来存储相应的数据。

创建分区的优势：

• 查询性能得以提升。特别是对于表中访问率较高的行位于一个单独分区或少数几个分区内，对于创建分区后，可以减少数据的搜索空间，提高数据访问效率。
• 当查询或更新一个分区的大部分记录时，仅对单独分区进行连续扫描，而并非对整个数据表进行访问，性能得以提升。
• 如果需要大量加载或者删除的记录位于单独的分区上，可以直接读取或删除该分区的数据。同时还可以避免由于大量DELETE导致的VACUUM超载（哈希分区不支持删除分区）。

注意事项

• 唯一约束和主键约束的约束键包含所有分区键将为约束创建LOCAL索引，否则创建GLOBAL索引。
• 目前哈希分区仅支持单列构建分区键，暂不支持多列构建分区键。
• 对于分区表PARTITION FOR (values)语法，values只能是常量。
• 对于分区表PARTITION FOR (values)语法，values在需要数据类型转换时，建议使用强制类型转换，以防隐式类型转换结果与预期不符。
• 分区数最大值为1048575个，一般情况下业务不可能创建1048575个分区，会导致内存不足。应参照参数local_syscache_threshold的值合理创建分区，分区表使用内存大致为（分区数 * 3 / 1024）MB。理论上分区占用内存不允许大于local_syscache_threshold的值，同时还需要预留部分空间以供其他功能使用。
• 考虑性能影响，一般建议单表最大分区数不超过2000，子分区数 * (LOCAL索引个数 + 1)不超过10000。
• 当分区数太多导致内存不足时，会间接导致性能急剧下降。
• 指定分区语句目前不能使用全局索引扫描。
• 不支持XML类型数据作为分区键、二级分区键。
• 对于分区表进行UPDATE或DELETE时，如果生成的计划不是FQS或Stream计划，语句执行效率会比较差。建议排查语句，消除不可下推因素，从而生成FQS或Stream计划。

• 在为数据对象增加或者变更ILM策略时，如果追加了行级表达式，目前仅支持白名单中列出的函数作为行级表达式，具体白名单函数列表请参见行表达式函数白名单。
  

  在ILM策略的ON ( EXPR )行级表达式支持的函数中，存在部分函数的输出可能会受兼容性参数影响。例如，upper函数在MYSQL兼容模式下设置b_format_version='5.7'和b_format_dev_version='s2'后，将无法转大写。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

CREATE TABLE [ IF NOT EXISTS ] partition_table_name
{ ( [ 
    { column_name data_type [ CHARACTER SET | CHARSET charset ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option [...] ] }
    [, ... ]
] )
    | LIKE source_table }
    [ table_option [ [ , ] ... ] ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]]
    [ TABLESPACE tablespace_name ]
    [ DISTRIBUTE BY { REPLICATION | HASH ( column_name [, ...] ) 
    | MURMURHASH ( diskey_expr )
    | RANGE ( column_name [, ...] ) { TO SLICEGROUP slicegroupname | 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 [, ... ] ) } ]
    PARTITION BY { 
        {RANGE [COLUMNS] (partition_key) [ PARTITIONS integer ] ( partition_less_than_item [, ... ] )} |
        {RANGE [COLUMNS] (partition_key) [ PARTITIONS integer ] ( partition_start_end_item [, ... ] )} |
        {LIST [COLUMNS] (partition_key) [ PARTITIONS integer ] ( PARTITION partition_name VALUES [IN] (list_values) [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]] [TABLESPACE [=] tablespace_name][, ... ])} |
        { HASH (partition_key) [ PARTITIONS integer ] ( PARTITION partition_name [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]] [TABLESPACE [=] tablespace_name][, ... ])}
    } [ { ENABLE | DISABLE } ROW MOVEMENT ]; 

• 其中table_option为：

  { COMMENT [ = ] 'string' |
    AUTO_INCREMENT [ = ] value |
    [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset |
    [ DEFAULT ] COLLATE [ = ] default_collation }

• 列约束column_constraint：

  1 2 3 4 5 6 7 8 9 10 11

  [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | AUTO_INCREMENT | COMMENT 'string' | DEFAULT default_expr | ON UPDATE update_expr | UNIQUE [KEY] [ index_parameters ] | PRIMARY KEY [ index_parameters] } [ DEFERRABLE | NOT DEFERRABLE ][ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

• 表约束table_constraint：

  [ CONSTRAINT constraint_name ]
  { CHECK ( expression ) | 
    UNIQUE [ index_name ] [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) [ index_parameters ] | 
    PRIMARY KEY ( column_name [, ... ] ) [ index_parameters]}
  [ DEFERRABLE | NOT DEFERRABLE ][ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
  { [ COMMENT 'string' ] [ ... ] }

• like选项like_option：

  1	{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | RELOPTIONS | DISTRIBUTION | UPDATE | ILM | ALL }

• 索引存储参数index_parameters：

  1 2	[ WITH ( {storage_parameter = value} [, ... ] ) ] [ USING INDEX TABLESPACE tablespace_name ]

• partition_less_than_item：

  1	PARTITION partition_name VALUES LESS THAN { ( { partition_value | MAXVALUE }[, ... ] ) | MAXVALUE } [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]] [TABLESPACE tablespace_name]

• partition_start_end_item：

  1 2 3 4 5 6

  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})} } [ ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER n { day | month | year } OF NO MODIFICATION [ ON ( EXPR )]] [TABLESPACE tablespace_name]

• 其中update_expr为：

  { CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }

参数说明

• IF NOT EXISTS

  判断是否存在相同名称的表。

  如果指定IF NOT EXISTS关键字，当已经存在相同名称的表时，不会新建也不会产生错误，而是返回NOTICE提示，告知表已存在。

• partition_table_name

  分区表的名称。

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

• column_name

  新表中要创建的字段名。

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

• data_type

  字段的数据类型。

• COLLATE collation

  指定列的字符序（数据类型需为支持排列的类型）。如果未指定，则使用默认的字符序。可以使用“SELECT * FROM pg_collation;”命令从pg_collation系统表中查询，默认的字符序为查询结果中以default开始的行。

• CONSTRAINT constraint_name

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

  定义约束有两种方法：

  • 列约束：作为一个列定义的一部分，仅影响该列。不支持为AUTO_INCREMENT | COMMENT 'string' | ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value )子句添加约束名。
  • 表约束：不和具体的列进行绑定，影响整个表的具体列，可以作用于多个列。
• LIKE source_table [ like_option ... ]

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

  • 新表与源表之间在创建动作完毕之后相互独立。在源表做的任何修改都不会继承到新表中，并且在扫描源表的时候，也不会包含新表的数据。
  • 字段默认表达式只有在指定了INCLUDING DEFAULTS时，才会继承到新表中。默认情况下，不包含默认表达式，即新表中所有字段的默认值都是NULL。
  • 源表上的非空约束将继承到新表中，CHECK约束仅在指定了INCLUDING CONSTRAINTS时才继承，而其他类型的约束则不会被继承。此规则同时适用于表约束和列约束。
  • 被继承的列和约束并不使用相同的名称进行融合。如果明确的指定了相同的名称或者在另外一个LIKE子句中，将会报错。
  • 如果指定了INCLUDING UPDATE，则源表列的ON UPDATE CURRENT_TIMESTAMP属性会继承到新表列中。默认情况下，不继承该属性。
  • 如果指定了INCLUDING INDEXES，则源表上的索引也将在新表上创建，默认情况下，不建立索引。
  • 如果指定了INCLUDING STORAGE，则源表列的STORAGE设置也将被继承，默认情况下，不包含STORAGE设置。
  • 如果指定了INCLUDING COMMENTS，则源表列、约束和索引的注释也会被继承。默认情况下，不对源表的注释进行继承。
  • 如果指定了INCLUDING RELOPTIONS，则源表的存储参数（即源表的WITH子句）也将继承至新表。默认情况下，不对源表的存储参数进行继承。
  • 如果指定了INCLUDING DISTRIBUTION，则新表将继承源表的分布信息，包括分布类型和分布列，同时新表将不能再使用DISTRIBUTE BY子句。默认情况下，不对源表的分布信息进行继承。
  • 如果指定了INCLUDING ILM，则源表的ILM策略信息会被继承到新表中，如果需要同时继承源表上的分区对象的ILM策略信息，需要同时指定INCLUDING PARTITION。
  • INCLUDING ALL是INCLUDING DEFAULTS、INCLUDING UPDATE、INCLUDING CONSTRAINTS、INCLUDING INDEXES、INCLUDING STORAGE、INCLUDING COMMENTS、INCLUDING RELOPTIONS、INCLUDING DISTRIBUTION和INCLUDING ILM的简写形式。
    

    • CREATE TABLE table_name LIKE source_table;语法仅在MYSQL模式数据库（即sql_compatibility = 'MYSQL'）下，且参数b_format_version值为5.7、b_format_dev_version值为s2时支持。
    • 在MYSQL模式数据库下，且参数b_format_version值为5.7、b_format_dev_version值为s2时，不支持指定INCLUDING和EXCLUDING选项，默认情况下与指定INCLUDING ALL的行为一致。
• AUTO_INCREMENT [ = ] value
  为自动增长列指定一个初始值，value为不得超过2¹²⁷-1的正整数。

  

  该子句仅在MYSQL模式数据库（即sql_compatibility = 'MYSQL'）下有效。

• COMMENT [ = ] 'string'

  为表添加注释。

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

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

• CHARACTER SET | CHARSET charset

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

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

• COLLATE collation

  指定列的字符序（数据类型需为支持排列的类型）。未单独指定CHARACTER时，会将字段的字符集设置为指定的字符序的默认字符集。

  可以使用“SELECT * FROM pg_collation;”语句从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。

  

  对于MYSQL模式数据库下（即sql_compatibility = 'MYSQL'）还支持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之间的数字。未指定时默认值为100（完全填充）。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页，每个页上的剩余空间将用于在该页上UPDATE操作。较小的填充因子对于UPDATE操作，可以在同一页上有机会放置同一条记录的新版本，相较于将新版本的数据放置在其他页上更为有效。对于一个从不更新的表，建议将填充因子设为100，对于频繁更新的表，建议选择较小的填充因子。

    取值范围：10~100

  • ORIENTATION

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

    取值范围：ROW，表示表的数据将以行式存储。

    默认值：ROW

  • STORAGE_TYPE

    指定存储引擎类型，该参数设置成功后就不再支持修改。当不指定STORAGE_TYPE时，存储引擎类型由参数enable_default_ustore_table决定，默认是Inplace-Update存储。

    取值范围：

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

    默认值：USTORE

  • statistic_granularity

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

    取值范围：具体请参见参数说明。

    默认值：AUTO。

  • enable_tde

    指定该表为加密表。数据库会自动将加密表中的数据先加密再存储。

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

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

    默认值：off

  • encrypt_algo

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

    有效值为：AES_128_CTR，SM4_CTR、AES_128_CTR_SHA256_HMAC、SM4_CTR_SM3_HMAC或AES_128_GCM。

    默认值：

    • 如果enable_tde=on：

      GUC参数tde_encrypt_config中子参数table_algorithm不为空，默认值与table_algorithm相同。

      GUC参数tde_encrypt_config中子参数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

    默认值：未指定时与GUC参数autovacuum_vacuum_threshold一致。

  • autovacuum_analyze_threshold

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

    取值范围：0~2147483647

    默认值：未指定时与GUC参数autovacuum_analyze_threshold一致。

  • autovacuum_vacuum_scale_factor

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

    取值范围：0.0~100.0

    默认值：未指定时与GUC参数autovacuum_vacuum_scale_factor一致。

  • autovacuum_analyze_scale_factor

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

    取值范围：0.0~100.0

    默认值：未指定时与GUC参数autovacuum_analyze_scale_factor一致。

  • autovacuum_freeze_min_age

    开启自动清理功能时，指定在该表参数指定了一个行版本的最小范围，超过这个范围的行会被冻结。

    取值范围：0~576460752303423487

    默认值：未指定时与GUC参数vacuum_freeze_min_age一致。

  • autovacuum_freeze_max_age

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

    取值范围：100000~576460752303423487

    默认值：未指定时与GUC参数autovacuum_freeze_max_age一致。

  • autovacuum_freeze_table_age

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

    取值范围：0~576460752303423487

    默认值：未指定时与GUC参数vacuum_freeze_table_age一致。

  • hashbucket

    创建hash bucket存储。本参数仅支持行存表和行存Range表。

    取值范围：on/off

    默认值：off

    

    当前版本hashbucket表相关DDL操作性能受限，不建议频繁对hashbucket表进行DDL操作。

• TABLESPACE tablespace_name

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

• DISTRIBUTE BY

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

  详细信息请参见DISTRIBUTE BY。

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

  指定创建表所在的Node Group。

  该语法仅供内部扩容工具使用，不建议用户直接使用。

• PARTITION BY RANGE [COLUMNS] (partition_key)

  创建范围（Range）分区，partition_key为分区键的名称。

  

  COLUMNS关键字只能在sql_compatibility='MYSQL'时使用，PARTITION BY RANGE COLUMNS语义同与PARTITION BY RANGE等价。

  • 对于从句是VALUES LESS THAN的语法格式：

    范围分区策略的分区键最多支持16列。

    分区键支持的数据类型为：

    • INTEGER TYPES：TINYINT、SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC
    • FLOAT TYPES：REAL、FLOAT4、FLOAT8、DOUBLE PRECISION
    • CHARACTER TYPES：CHARACTER VARYING、VARCHAR、CHARACTER、CHAR、TEXT、NVARCHAR2、NAME
    • DATE/TIME TYPES：TIMESTAMP、DATE
  • 对于从句是START END的语法格式：

    范围分区策略的分区键仅支持1列。

    分区键支持的数据类型为：

    • INTEGER TYPES：TINYINT、SMALLINT、INTEGER、BIGINT、DECIMAL、NUMERIC
    • FLOAT TYPES：REAL、FLOAT4、FLOAT8、DOUBLE PRECISION
    • DATE/TIME TYPES：TIMESTAMP、DATE
• PARTITION partition_name VALUES LESS THAN {( { partition_value | MAXVALUE } [,...] ) | MAXVALUE }

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

  partition_name：范围分区的名称。

  partition_value：范围分区的上边界，取值依赖于partition_key的类型。

  MAXVALUE：表示分区的上边界，通常用于设置最后一个范围分区的上边界。

  

  • 每个分区都需要指定一个上边界。
  • 分区上边界的类型应当和分区键的类型一致。
  • 分区列表是按照分区上边界升序排列的，值较小的分区位于值较大的分区之前。
  • 不在括号内的MAVALUE，只能在sql_compatibility='MYSQL'时使用，并且只能有一个分区键。
• 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：表示最大值，它通常用于设置最后一个范围分区的上边界。
  

  • 在创建分区表若第一个分区定义含START值，则范围(MINVALUE, START)将自动作为实际的第一个分区。
  • 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创建的每个分区所属的表空间相同。
    • partition_name作为分区名称前缀时，其长度不要超过57字节，超过时自动截断。
    • 在创建或修改分区表时，分区表的分区总数不可超过最大限制（1048575）。
  • 在创建分区表时START END与LESS THAN语法不可混合使用。
  • 即使创建分区表时使用START END语法，备份（gs_dump）操作的SQL语句仍为VALUES LESS THAN语法格式。
• PARTITION BY LIST [COLUMNS] (partition_key)

  创建列表（List）分区，partition_key为分区键的名称。

  

  • COLUMNS关键字只能在sql_compatibility='MYSQL'时使用，PARTITION BY LIST COLUMNS语义与PARTITION BY LIST等价。
  • 对于从句为PARTITION partition_name VALUES [IN] (list_values)的语法格式，仅支持在sql_compatibility='MYSQL'时使用，语义与VALUES等价。list_values中包含了对应分区存在的键值，每个分区的键值数量不超过64个。
  • 列表分区策略的分区键最多支持16列。
  • 分区键支持的数据类型为：
    • INTEGER TYPES：TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC
    • CHARACTER TYPES：VARCHAR、CHAR、BPCHAR、NVARCHAR2
    • DATE/TIME TYPES：TIMESTAMP、DATE
  • 分区个数不能超过1048575个。
• PARTITION BY HASH(partition_key)

  创建哈希（Hash）分区，partition_key为分区键的名称。

  • 哈希分区策略的分区键仅支持1列。
  • 分区键支持的数据类型为：
    • INTEGER TYPES：TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC
    • CHARACTER TYPES：VARCHAR、CHAR、BPCHAR、TEXT、NVARCHAR2
    • DATE/TIME TYPES：TIME、TIMESTAMP、DATE
  • 分区个数不能超过1048575个。
• PARTITIONS integer

  指定分区个数。integer为分区数，范围必须为(0, 1048575]的整数。

  • 当在范围分区和列表分区后指定此子句时，必须显式定义每个分区，且定义分区的数量必须与integer值相等。只能在sql_compatibility='MYSQL'时，在范围分区和列表分区后指定此子句。
  • 当在哈希分区后指定此子句时，若不指定各个分区的定义，将自动生成integer个分区，自动生成的分区名为“p数字”（其中，数字：依次为0~(integer-1)），分区的表空间默认为此表的表空间；若显式列出每个分区定义，此时定义分区的数量必须与integer值相等；若既不列出分区定义，也不指定分区数量，系统将会创建出唯一一个分区。
• { ENABLE | DISABLE } ROW MOVEMENT

  行迁移开关。如果进行UPDATE操作时，更新了元组在分区键上的值，造成了该元组所在分区发生变化，根据指定的ENABLE或DISABLE的行为进行报错或进行元组在分区间的转移。

  取值范围：

  • ENABLE：行迁移开关打开。
  • DISABLE：默认值，行迁移开关关闭。
  

  在打开行迁移开关情况下，并发进行UPDATE、DELETE操作产生报错，可能的原因为：在UPDATE和DELETE操作中，对于操作前的数据都是标记为已删除状态。当打开行迁移开关，如果更新分区键时，导致了跨分区更新，数据库会将旧分区中旧数据标记为已删除，在新分区中新增加一条数据，无法通过旧数据找到新数据。

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

  • 对于数据非跨分区结果，第一个操作执行完后，第二个操作不会报错。

    如果第一个操作是UPDATE，第二个操作能成功找到最新的数据，之后对新数据操作。

    如果第一个操作是DELETE，第二个操作看到当前数据已经被删除而且找不到最新数据，就终止操作。

  • 对于数据跨分区结果，第一个操作执行完后，第二个操作会报错。

    如果第一个操作是UPDATE，由于新数据在新分区中，第二个操作不能成功找到最新的数据，就无法操作，之后会报错。

    如果第一个操作是DELETE，第二个操作看到当前数据已经被删除而且找不到最新数据，但无法判断删除旧数据的操作是UPDATE还是DELETE。如果是UPDATE，报错处理。如果是DELETE，终止操作。为了保持数据的正确性，只能报错处理。

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

范围分区

• VALUES LESS THAN

  --创建表空间。
  CREATE TABLESPACE tbs_test_range1_p1 RELATIVE LOCATION 'tbs_test_range1/tablespace_1';
  CREATE TABLESPACE tbs_test_range1_p2 RELATIVE LOCATION 'tbs_test_range1/tablespace_2';
  CREATE TABLESPACE tbs_test_range1_p3 RELATIVE LOCATION 'tbs_test_range1/tablespace_3';
  CREATE TABLESPACE tbs_test_range1_p4 RELATIVE LOCATION 'tbs_test_range1/tablespace_4';
  
  --创建分区表test_range1。
  CREATE TABLE test_range1(
      id INT, 
      info VARCHAR(20)
  ) PARTITION BY RANGE (id) (
      PARTITION p1 VALUES LESS THAN (200) TABLESPACE tbs_test_range1_p1,
      PARTITION p2 VALUES LESS THAN (400) TABLESPACE tbs_test_range1_p2,
      PARTITION p3 VALUES LESS THAN (600) TABLESPACE tbs_test_range1_p3,
      PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_test_range1_p4
  );
  
  --插入1000条数据。
  INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');
  
  --查看p1分区的行数199条，[1,200)。
  SELECT COUNT(*) FROM test_range1 PARTITION (p1);
   count 
  -------
     199
  (1 row)
  
  --查看p2分区的行数200条，[200,400)。
  SELECT COUNT(*) FROM test_range1 PARTITION (p2);
   count 
  -------
     200
  (1 row)
  
  --查看分区信息。
  SELECT a.relname, a.boundaries, b.spcname  
  FROM pg_partition a, pg_tablespace b 
  WHERE a.reltablespace = b.oid AND a.parentid = 'test_range1'::regclass;
   relname | boundaries |      spcname       
  ---------+------------+--------------------
   p1      | {200}      | tbs_test_range1_p1
   p2      | {400}      | tbs_test_range1_p2
   p3      | {600}      | tbs_test_range1_p3
   pmax    | {NULL}     | tbs_test_range1_p4
  (4 rows)
  
  --删除。
  DROP TABLE test_range1;
  DROP TABLESPACE tbs_test_range1_p1;
  DROP TABLESPACE tbs_test_range1_p2;
  DROP TABLESPACE tbs_test_range1_p3;
  DROP TABLESPACE tbs_test_range1_p4;

• START END

  --创建分区表。
  CREATE TABLE test_range2(
      id INT, 
      info VARCHAR(20)
  ) PARTITION BY RANGE (id) (
      PARTITION p1 START(1) END(600) EVERY(200),    
      PARTITION p2 START(600) END(800),
      PARTITION pmax START(800) END(MAXVALUE)
  );
  
  --查看分区信息。
  SELECT relname, boundaries FROM pg_partition WHERE parentid = 'test_range2'::regclass AND parttype = 'p' ORDER BY 1;
   relname | boundaries 
  ---------+------------
   p1_0    | {1}
   p1_1    | {201}
   p1_2    | {401}
   p1_3    | {600}
   p2      | {800}
   pmax    | {NULL}
  (6 rows)
  
  --删除。
  DROP TABLE test_range2;

列表分区

--创建列表分区表。
CREATE TABLE test_list ( NAME VARCHAR ( 50 ), area VARCHAR ( 50 ) ) 
PARTITION BY LIST (area) (
    PARTITION p1 VALUES ('Beijing'),
    PARTITION p2 VALUES ('Shanghai'),
    PARTITION p3 VALUES ('Guangzhou'),
    PARTITION p4 VALUES ('Shenzhen'),
    PARTITION pdefault VALUES (DEFAULT)
);

--插入数据。
INSERT INTO test_list VALUES ('bob', 'Shanghai'),('scott', 'Sichuan');

--查询分区数据。
SELECT * FROM test_list PARTITION (p2);
 name |   area   
------+----------
 bob  | Shanghai
(1 row)
SELECT * FROM test_list PARTITION (pdefault);
 name  |  area   
-------+---------
 scott | Sichuan
(1 row)

--删除。
DROP TABLE test_list;

哈希分区

--创建哈希分区表，指定分区数。
CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;

--创建哈希分区表，并指定分区名。
CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(C1)(
    PARTITION pa,
    PARTITION pb,
    PARTITION pc
);

--查看分区信息。
SELECT b.relname AS table_name,
       a.relname AS partition_name 
FROM pg_partition a, 
     pg_class b 
WHERE b.relname LIKE 'test_hash%' 
  AND a.parttype = 'p' 
  AND a.parentid = b.oid; 
 table_name | partition_name 
------------+----------------
 test_hash1 | p2
 test_hash1 | p1
 test_hash1 | p0
 test_hash2 | pc
 test_hash2 | pb
 test_hash2 | pa
(6 rows)

--删除。
DROP TABLE test_hash1,test_hash2;

相关链接

ALTER TABLE PARTITION，DROP TABLE