CREATE TABLE PARTITION

IF NOT EXISTS

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

-

partition_table_name

分区表的名称。

字符串，要符合标识符的命名规范。参见标识符命名规范。

column_name

新表中要创建的字段名。

字符串，要符合标识符的命名规范。参见标识符命名规范。

data_type

字段的数据类型。

-

COLLATE collation

COLLATE子句指定列的排序规则，仅支持char、varchar、text、nchar、nvarchar这些数据类型的排序规则，排序规则主要影响查询结果的排列顺序，例如SELECT ... ORDER BY后展示的优先级。如果没有指定，则使用默认的排序规则C（即严格区分大小写）。

常见的排序规则有：

• C：基于二进制编码的字节顺序进行排序，完全区分大小写、重音和特殊字符（严格按ASCII 码值比较）。
• case_insensitive：不区分大小写，8.1.3.x及以上版本支持。
• en_US.utf8：美式英语UTF8编码，不区分大小写。

DWS支持的排序规则可通过系统表PG_COLLATION 查询：

1

SELECT * FROM pg_collation;

查看当前数据库默认的排序规则可使用如下语句：

1

SELECT pg_database.datcollate AS current_collation FROM pg_catalog.pg_database WHERE pg_database.datname = pg_catalog.current_database();

例如，对某列的取值采用不区分大小写排序规则。

1
2
3

CREATE TABLE t1 (a text collate case_insensitive);
INSERT INTO t1 values('a'),('A'),('b'),('B');
SELECT a, a='a' FROM t1;

查询时结果如下，表示A和a等价。

CONSTRAINT constraint_name

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

定义约束有两种方法：

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

支持的约束参见表3。

LIKE source_table [ like_option ... ]

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

使用约束参见LIKE参数使用说明。

WITH

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

支持的WITH参数参见表4。

COMPRESS / NOCOMPRESS

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

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

DISTRIBUTE BY

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

取值范围：

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

其他详细使用约束参见DISTRIBUTE BY参数使用说明。

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

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

-

{ ENABLE | DISABLE } ROW MOVEMENT

行迁移开关，当更新表中某行分区键值时，如果新值属于其他分区，则会根据开关给出报错信息或者将该行数据迁移到对应的分区。

• ENABLE：打开行迁移功能，适用于频繁更新分区键的场景。
• DISABLE：关闭行迁移功能，适用于严格禁止数据跨分区更新的场景，更新分区键会报错。

默认为DISABLE，关闭。

使用建议：

• 启用条件：当业务需要通过更新操作改变分区键值，且性能影响可接受时使用。
• 性能考量：ENABLE ROW MOVEMENT会增加更新操作的开销，特别是在高并发场景下需谨慎使用。
• 替代方案：若需频繁调整分区归属，可考虑先删除原行再插入新行，或使用分区交换（Exchange Partition）操作。

举例，某分区表的日期列（分区键），按季度进行分区，分为分区p_2023q1和p_2023q2，某一行的“2023-02-15”原属于第一季度的分区p_2023q1，当更新该值为“2023-05-15”后，对应的这行数据，会根据ROW MOVEMENT开关是否打开而决定是否行数据迁移：

• ENABLE：打开行迁移功能，则该数据迁移到新分区p_2023q2。
• DISABLE：关闭行迁移功能，则系统会报错。

详细示例参见示例：开启和关闭行迁移功能ROW MOVEMENT

NOT NULL

字段值不允许为NULL。ENABLE用于语法兼容，可省略。

-

NULL

字段值允许NULL ，这是默认值，该子句只是为和非标准SQL数据库兼容。不建议使用。

-

CHECK (condition) [ NO INHERIT ]

CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会报错并且执行失败。

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

用NO INHERIT标记的约束将不会传递到子表中去。

单个字段约束:

价格必须为正数：

1
2
3
4

CREATE TABLE products (
    product_id INT,
    price DECIMAL(10,2) CHECK (price > 0), 
    discount DECIMAL(3,2) );

插入一条价格为负数的数据：

1

INSERT INTO products (product_id, price, discount) VALUES (2, -10.00, 1.1);

违反约束，则报错，插入不成功。

1

ERROR:  dn_6003_6004: new row for relation "products" violates check constraint "products_price_check"

表约束（约束多个字段）：

创建一个库存表，分两个字段，可用量和预定量，且可用量必须大于等于预定量：

1
2
3
4
5

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    quantity_on_hand INT,
    quantity_reserved INT,
    CHECK (quantity_on_hand >= quantity_reserved));

DEFAULT default_expr

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

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

-

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值且与原索引列中已有数据不相等的数据可插入。
• NULLS IGNORE：在等值比较时跳过NULL值。若索引列全为NULL，则NULL值可重复插入；部分索引列为NULL，只有非NULL值不相等，才可成功插入数据。

三种处理方式具体的行为如表8所示。

更多使用方法可以参见关于NULLS DISTINCT、NULLS NOT DISTINCT、NULLS IGNORE使用上的场景差异。

PRIMARY KEY index_parameters

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

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

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

-

DEFERRABLE | NOT DEFERRABLE

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

-

INITIALLY IMMEDIATE | INITIALLY DEFERRED

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

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

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

-

FILLFACTOR

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

10~100

ORIENTATION

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

• COLUMN：表的数据将以列式存储。
• ROW（缺省值）：表的数据将以行式存储。
• ORC：表的数据将以ORC格式存储（仅HDFS表）。

COMPRESSION

该参数仅列存表支持，有效值为：LOW、MIDDLE或HIGH。默认值为LOW。

-

MAX_BATCHROW

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

取值范围：10000~60000

默认值：60000

PARTIAL_CLUSTER_ROWS

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

取值范围：其有效值为大于等于10万。此值是MAX_BATCHROW的倍数。

enable_delta

指定了在列存表是否开启delta表。该参数只对列存表有效。不推荐使用列存带Delta表，否则会出现由于来不及merge而导致的磁盘膨胀以及性能劣化等问题。

默认值：off

DELTAROW_THRESHOLD

预留参数。该参数只对列存表有效。

取值范围：0～60000，默认值为6000

COLD_TABLESPACE

指定冷分区保存的OBS表空间，仅冷热表支持。该参数仅支持列存分区表，且该参数不支持修改，需与STORAGE_POLICY同时使用。在指定STORAGE_POLICY时，可不设置该参数，默认为default_obs_tbs。

取值范围：有效的OBS TABLESPACE名。

STORAGE_POLICY

指定冷热分区切换规则，仅冷热表支持。该参数需与COLD_TABLESPACE同时使用。

取值范围："冷热切换策略名称:冷热切换的阈值"，目前冷热切换的策略名称只支持LMT和HPN，LMT指按分区的最后更新时间切换，HPN指保留热分区的个数切换。

• LMT：[day]：表示切换[day]时间前修改的热分区数据为冷分区，将该数据迁至OBS表空间中。其中[day]为整型，范围[0, 36500]，单位为天。
• HPN: [hot_partition_num]：表示保留[hot_partition_num]个有数据的分区为热分区。保留规则为查找出有数据的分区的最大的Sequence ID，大于Sequence ID的无数据分区为热分区，并按这个Sequence ID从大到小保留[hot_partition_num]个分区为热分区；分区Sequence ID小于保留的最小热分区的Sequence ID的分区为冷分区，在冷热切换时，需要将数据迁移至OBS表空间中。其中[hot_partition_num]为整型，范围为[0,1600]。

ENABLE_MULTI_TEMP_DISKCACHE

冷热表性能优化参数，仅支持冷热表设置，该参数仅9.1.0及以上版本支持。

取值范围：on/true，off/false

默认值：on/true

PERIOD

指定分区管理中自动创建分区的周期，并开启自动创建分区功能。仅支持行存、列存范围分区表、时序表以及冷热表；分区键唯一并且类型仅支持TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE；不支持存在maxvalue分区；(nowTime - boundaryTime) / PERIOD需要小于分区个数上限，其中nowTime为当前时间，boundaryTime为现有分区中最早的分区边界时间；不支持在小型机、加速集群上使用。

取值范围：1 hour ~ 100 years

TTL

指定分区管理中分区过期的时间，并开启自动删除分区功能。不支持单独设置，必须要提前或同时设置PERIOD，并且要大于或等于PERIOD。

取值范围：1 hour ~ 100 years

COLVERSION

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

取值范围：

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

默认值：2.0

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

SKIP_FPI_HINT

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

默认值：false

PARTITION BY RANGE(partition_key)

指定范围分区策略语法，partition_key为分区键的名称。

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

  对于从句是VALUE LESS THAN的语法格式，范围分区策略的分区键最多支持4列，且分区键只能是列名。当存在多个分区键时，一个列名只能出现一次，且相邻的两个分区键要使用逗号隔开。

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

• 对于从句是START END的语法格式：
  注意：

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

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

PARTITION BY LIST (partition_key,[...])

指定列表分区策略语法，partition_key为分区键的名称。

列表分区策略分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、NUMERIC/DECIMAL、TEXT、NVARCHAR2、VARCHAR(n)、CHAR、BPCHAR、TIME、TIME WITH TIMEZONE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、DATE、INTERVAL、SMALLDATETIME。

partition_less_than_item

-

partition_start_end_item

使用起始值以及间隔值定义范围分区的语法。

1
2
3
4

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字节，超过时自动截断；
     • 在创建、修改分区表时请注意分区表的分区总数不可超过最大限制（32767）；
  3. 在创建分区表时START END与LESS THAN语法不可混合使用。
  4. 即使创建分区表时使用START END语法，备份（gs_dump）出的SQL语句也是VALUES LESS THAN语法格式。

list_partition_item

列表分区策略下分区（简称为列表分区）的定义语法。partition_name为分区的名称。partition_value为列表分区边界的一个枚举值，取值依赖于partition_key的类型。DEFAULT表示默认分区的边界。

1

PARTITION partition_name VALUES ( { (partition_value) [, ... ] | DEFAULT } )

对于列表分区表，存在以下约定和约束：

• 边界值为DEFAULT的分区，称之为默认分区。
• 每个列表分区表只能有一个DEFAULT分区。
• 分区表的所有分区数不超过32767个，所有分区的边界值个数不大于32767个。
• 不管分区键的个数，DEFAULT分区的边界只能是一个DEFAULT。
• 如果分区键由多个字段组成，每个partition_value需要包含所有分区键的值，当分区键只有一列时，partition_value两侧的括号可以省略，参见示例：创建多个分区键的list分区表。
• 如果分区键由多个字段组成，比较大小时，先逐个字段比较大小，任何一个字段值不一样即可认为是不一样的键值。
• 边界中不同的partition_value值不能重复。
• 数据插入时，如果数据的分区键值能匹配任何非DEFAULT分区的边界，那么数据会写入对应的分区；否则数据会写入DEFAULT分区。

NULLS DISTINCT

可重复插入

可重复插入

NULLS NOT DISTINCT

不可重复插入

非NULL值相等，不可插入；非NULL值不相等，则插入成功

NULLS IGNORE

可重复插入

非NULL值相等，不可插入；非NULL值不相等，则插入成功

1hour

Hour

2022-02-17 17:00:00

1day

Day

2022-02-18 00:00:00

1month

Month

2022-03-01 00:00:00

13month

Year

2023-01-01 00:00:00

NULLS DISTINCT

可重复插入

可重复插入

NULLS NOT DISTINCT

不可重复插入

非NULL值相等，不可插入；非NULL值不相等，则插入成功

NULLS IGNORE

可重复插入

非NULL值相等，不可插入；非NULL值不相等，则插入成功

1hour

Hour

2022-02-17 17:00:00

1day

Day

2022-02-18 00:00:00

1month

Month

2022-03-01 00:00:00

13month

Year

2023-01-01 00:00:00