CREATE TABLE PARTITION

IF NOT EXISTS

Does not throw an error if a table with the same name exists. A notice is issued in this case.

-

partition_table_name

Name of the partitioned table

A string, which must comply with the naming convention. For details, see Identifier Naming Conventions.

column_name

Specifies the name of a column to be created in the new table.

A string, which must comply with the naming convention. For details, see Identifier Naming Conventions.

data_type

Specifies the data type of the column.

-

COLLATE collation

The COLLATE clause defines the collation rule of a column. The supported data types include char, varchar, text, nchar, and nvarchar. The collation rule significantly impacts the sequence of query results, particularly when using SELECT ... ORDER BY. If no collation rule is specified, the default collation rule C (case-sensitive) is used.

Common collation rules:

• C: The collation uses the byte order of the binary code and is sensitive to case, accents, and special characters. Data can indeed be compared based on its ASCII code values.
• case_insensitive: The collation is case-insensitive. It is supported by clusters of version 8.1.3.x or later.
• en_US.utf8: The collation uses the American English with a UTF-8 encoding and is case insensitive.

You can query collation rules supported by DWS from the PG_COLLATION system catalog.

1

SELECT * FROM pg_collation;

To check the default collation rule of the current database, run the following statement:

1

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

A case-insensitive collation rule for the values of a column

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;

The following figure shows that A and a are equivalent.

CONSTRAINT constraint_name

Specifies a name for a column or table constraint. The optional constraint clauses specify constraints that new or updated rows must satisfy for an insert or update operation to succeed.

There are two ways to define constraints:

• A column constraint is defined as part of a column definition, and it is bound to a particular column.
• A table constraint is not bound to any particular columns but can apply to more than one column.

For details about the supported constraints, see Table 3.

LIKE source_table [ like_option ... ]

Specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

For details about the supported constraints, see LIKE Parameters.

WITH

Specifies an optional storage parameter for a table or an index.

For details about the supported WITH parameters, see Table 4.

COMPRESS / NOCOMPRESS

Specifies the keyword COMPRESS during the creation of a table, so that the compression feature is triggered in the case of a bulk INSERT operation. If this feature is enabled, a scan is performed for all tuple data within the page to generate a dictionary and then the tuple data is compressed and stored. If NOCOMPRESS is specified, the table is not compressed.

Default value: NOCOMPRESS, tuple data is not compressed before storage.

DISTRIBUTE BY

Specifies how the table is distributed or replicated between DNs.

• REPLICATION: Each row in the table exists on all DNs, that is, each DN has complete table data.
• ROUNDROBIN: Each row in the table is sent to each DN in turn. Therefore, data is evenly distributed on each DN. This value is supported only in 8.1.2 or later.
• HASH (column_name): Each row of the table will be placed into all the DNs based on the hash value of the specified column.

For details about other restrictions, see DISTRIBUTE BY Parameter Description.

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

TO GROUP specifies the Node Group in which the table is created. Currently, it cannot be used for HDFS tables. TO NODE is used for internal scale-out tools.

-

{ ENABLE | DISABLE } ROW MOVEMENT

Specifies whether to relocate a row within a table. If the new value of a partition key in a row belongs to another partition, an error message is displayed or the row is moved to the corresponding partition.

• ENABLE: The row is moved to the corresponding partition. If partition keys are frequently updated, enable this parameter.
• DISABLE: The row is not moved to the corresponding partition. If partition keys are updated, an error is reported. If data cross-partition update is strictly prohibited, disable this parameter.

The default value is DISABLE.

Suggestions:

• Scenario: To enable row movement, you need to change partition key values through UPDATE operations and can accept its impact on performance.
• Performance: Enabling row movement will increase the overhead of UPDATE operations. Exercise caution when enabling this parameter in high-concurrency scenarios.
• Alternative solution: If you need to frequently adjust the partition ownership, delete the original row and then insert a new row, or exchange partitions.

The date column (partition key) of a partitioned table is partitioned by quarter into p_2023q1 and p_2023q2. The value 2023-02-15 of a row belongs to the partition p_2023q1 in the first quarter. After the value is updated to 2023-05-15, you can configure ROW MOVEMENT to determine whether to move the data in this row.

• ENABLE: The row movement is enabled. The data is moved to the new partition p_2023q2.
• DISABLE: The row movement is disabled. The system reports an error.

For details about examples, see Examples: Enabling and Disabling ROW MOVEMENT.

NOT NULL

Specifies that the column is not allowed to contain NULL values.

-

NULL

Specifies that the column can store NULL values. This is the default. This clause is only provided for compatibility with non-standard SQL databases. You are advised not to use this clause.

-

CHECK (condition) [ NO INHERIT ]

Specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed, or an error is reported and the execution fails.

A check constraint specified as a column constraint should reference only the column's values, while an expression appearing in a table constraint can reference multiple columns.

A constraint marked with NO INHERIT will not propagate to child tables.

Single-column constraints:

The price must be a positive number.

1
2
3
4

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

Insert a data record where the price is a negative number.

1

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

The constraint is violated, so an error is reported and the insertion fails.

1

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

Table constraints (on multiple columns):

Create an inventory table with two columns available and reserved. One column indicates available quantity and the other column indicates reserved quantity. The available quantity must be greater than or equal to the reserved quantity.

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

Assigns a default data value for a column. The value can be any variable-free expressions (Subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the data type of the column.

The default expression will be used in any insert operation that does not specify a value for the column. If there is no default value for a column, then the default value is NULL.

-

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

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

Specifies that a group of one or more columns of a table can contain only unique values.

The [ NULLS [ NOT ] DISTINCT | NULLS IGNORE ] field is used to specify how to process null values in the index column of the Unique index.

Default value: NULL can be inserted repeatedly.

When the inserted data is compared with the original data in the table, the NULL value can be processed in any of the following ways:

• NULLS DISTINCT: Each NULL value is regarded as a different value, that is, NULL values can be inserted repeatedly.
• NULLS NOT DISTINCT: All NULL values are regarded as the same value. That is, only one NULL value entry is retained in the index. NULL values cannot be inserted repeatedly. Only distinct non-NULL values can be inserted.
• NULLS IGNORE: NULL values are skipped during the equivalent comparison. If all index columns are NULL, NULL values can be inserted repeatedly. If some index columns are NULL, data can be inserted only when Not NULL values are different.

Table 6 lists the behaviors of the three processing modes.

For details, see Application Differences Among NULLS DISTINCT, NULLS NOT DISTINCT, and NULLS IGNORE.

PRIMARY KEY index_parameters

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

Specifies the primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.

Only one primary key can be specified for a table.

-

DEFERRABLE | NOT DEFERRABLE

Sets whether the constraint can be deferrable. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction using the SET CONSTRAINTS command. NOT DEFERRABLE is the default value. Currently, only UNIQUE and PRIMARY KEY constraints of row-store tables accept this clause. All the other constraints are not deferrable.

-

INITIALLY IMMEDIATE | INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint.

• If the constraint is INITIALLY IMMEDIATE (default value), it is checked after each statement.
• If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.

The constraint check time can be altered using the SET CONSTRAINTS command.

-

FILLFACTOR

The fillfactor of a table is a percentage between 10 and 100. 100 (complete packing) is the default value. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage. The remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page, which is more efficient than placing it on a different page. For a table whose records are never updated, setting the fillfactor to 100 (complete packing) is the appropriate choice, but in heavily updated tables smaller fillfactors are appropriate. The parameter has no meaning for column-store tables.

10~100

ORIENTATION

Determines the storage mode of the data in the table.

• COLUMN: The data will be stored in columns.
• ROW (default value): The data will be stored in rows.
• ORC: The data of the table will be stored in ORC format (only HDFS table).

COMPRESSION

This parameter is available only for column-store tables. Value: LOW (default value), MIDDLE, or HIGH.

-

MAX_BATCHROW

Specifies the maximum of a storage unit during data loading process. The parameter is only valid for column-store tables.

10000 to 60000

Default value: 60000

PARTIAL_CLUSTER_ROWS

Specifies the number of records to be partial cluster stored during data loading process. The parameter is only valid for column-store tables.

The value is no less than 100,000. The value is the multiple of MAX_BATCHROW.

enable_delta

Specifies whether to enable delta tables in column-store tables. The parameter is only valid for column-store tables. Using column-store tables with delta tables is not recommended. This may cause disk bloat and performance deterioration due to delayed merge.

Default value: off

DELTAROW_THRESHOLD

A reserved parameter. The parameter is only valid for column-store tables.

0 to 60000

Default value: 6000

COLD_TABLESPACE

Specifies the OBS tablespace for the cold partitions in a hot or cold table. This parameter is available only to column-store partitioned tables and cannot be modified. It must be used together with STORAGE_POLICY. The parameter STORAGE_POLICY can be left unconfigured. In this case, the default value default_obs_tbs is used.

Valid OBS tablespace name.

STORAGE_POLICY

Specifies the rule for switching between hot and cold partitions. This parameter is used only for multi-temperature tables. This parameter must be used together with COLD_TABLESPACE.

Value range: Cold and hot switchover policy name:Cold and hot switchover threshold. Currently, only LMT and HPN policies are supported. LMT indicates that the switchover is performed based on the last update time of partitions. HPN indicates the switchover is performed based on a fixed number of reserved hot partitions.

• LMT:[day]: Switch the hot partition data that is not updated in the last [day] days to the OBS tablespace as cold partition data. [day] is an integer ranging from 0 to 36500, in days.
• HPN:[hot_partition_num]: [hot_partition_num] indicates the number of hot partitions (with data) to be retained. The rule is to find the maximum sequence ID of the partitions with data. The partitions without data whose sequence ID is greater than the maximum sequence ID are hot partitions, and [hot_partition_num] partitions are retained as hot partitions in descending order according to the sequence ID. A partition whose sequence ID is smaller than the minimum sequence ID of the retained hot partition is a cold partition. During hot and cold partition switchover, data needs to be migrated to the OBS tablespace. [hot_partition_num] is an integer ranging from 0 to 1600.

ENABLE_MULTI_TEMP_DISKCACHE

This parameter improves the performance of tables that separate cold and hot data. It is only applicable to tables that support this feature and is supported in versions 9.1.0 and later.

on or off

Default value: on/true

PERIOD

Specifies the period of automatically creating partitions and enables the automatic partition creation function. Only row-store and column-store range partitioned tables, time series tables, and cold and hot tables are supported. The partition key must be unique and its type can only be TIMESTAMP[(p)] [WITHOUT TIME ZONE], TIMESTAMP[(p)] [WITH TIME ZONE] or DATE. MAXVALUE partitions are not supported. The value of (nowTime - boundaryTime)/PERIOD must be less than the upper limit of the number of partitions, where nowTime indicates the current time and boundaryTime indicates the earliest partition boundary time. It cannot be used on midrange computers and acceleration clusters.

1 hour ~ 100 years

TTL

Specifies the partition expiration time in partition management and enables the automatic partition deletion function. This parameter cannot be set separately. You must set PERIOD in advance or at the same time. The value of this parameter must be greater than or equal to that of PERIOD.

1 hour ~ 100 years

COLVERSION

Specifies the version of the column-store format. Switching between different storage formats is supported. However, the storage format of a partitioned table cannot be switched.

• 1.0: Each column in a column-store table is stored in a separate file. The file name is relfilenode.C1.0, relfilenode.C2.0, relfilenode.C3.0, or similar.
• 2.0: All columns of a column-store table are combined and stored in a file. The file is named relfilenode.C1.0.

Default value: 2.0

The value of COLVERSION can only be set to 2.0 for OBS multi-temperature tables.

SKIP_FPI_HINT

Indicates whether to skip the hint bits operation when the full-page writes (FPW) log needs to be written during sequential scanning.

PARTITION BY RANGE(partition_key)

The syntax specifying range partitioning. partition_key indicates the name of a partition key.

• Assume that the VALUES LESS THAN syntax is used.
  CAUTION:

  In this case, a maximum of four partition keys are supported, and the partition key must be a column name. If there are multiple partition keys, a column name can appear only once, and two adjacent partition keys must be separated by a comma (,).

  Data types supported by the partition keys are as follows: 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], and DATE.

• Assume that the START END syntax is used.
  CAUTION:

  In this case, only one partition key is supported.

  Data types supported by the partition key are as follows: SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, TIMESTAMP[(p)] [WITHOUT TIME ZONE], TIMESTAMP[(p)] [WITH TIME ZONE], and DATE.

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

The syntax specifying list partitioning. partition_key indicates the name of a partition key.

In list partitioning, partition keys support the following data types: TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC/DECIMAL, TEXT, NVARCHAR2, VARCHAR(n), CHAR, BPCHAR, TIME, TIME WITH TIMEZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, DATE, INTERVAL and SMALLDATETIME.

partition_less_than_item

Partition definition syntax in range partitioning. partition_name is the name of a range partition. partition_value is the upper limit of range partition, and the value depends on the type of partition_key. MAXVALUE can specify the upper boundary of a range partition, and it is commonly used to specify the upper boundary of the last range partition.

1

PARTITION partition_name VALUES LESS THAN ( { partition_value | DEFAULT } )

-

partition_start_end_item

The syntax of using the start value and interval value to define a range partition.

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)}

The following table describes the parameters.

• partition_name: name or name prefix of a range partition. It is the name prefix only in the following cases (assuming that partition_name is p1):
  • If START+END+EVERY is used, the names of partitions will be defined as p1_1, p1_2, and the like. For example, if PARTITION p1 START(1) END(4) EVERY(1) is defined, the generated partitions are [1, 2), [2, 3), and [3, 4), and their names are p1_1, p1_2, and p1_3. In this case, p1 is a name prefix.
  • If the defined statement is in the first place and has START specified, the range (MINVALUE, START) will be automatically used as the first actual partition, and its name will be p1_0. The other partitions are then named p1_1, p1_2, and the like. For example, if PARTITION p1 START(1), PARTITION p2 START(2) is defined, generated partitions are (MINVALUE, 1), [1, 2), and [2, MAXVALUE), and their names will be p1_0, p1_1, and p2. In this case, p1 is a name prefix and p2 is a partition name. MINVALUE means the minimum value.
• partition_value: start point value or end point value of a range partition. The value depends on partition_key and cannot be MAXVALUE.
• interval_value: width of each partition for dividing the [START, END) range. It cannot be MAXVALUE. If the value of (END – START) divided by EVERY has a remainder, the width of only the last partition is less than the value of EVERY.
• MAXVALUE: maximum value. It is usually used to set the upper boundary for the last range partition.

list_partition_item

Partition definition syntax in list partitioning. partition_name indicates the partition name. partition_value is an enumerated value of the list partition boundary. The value depends on the type of partition_key. DEFAULT indicates the default partition boundary.

1

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

The following conventions and constraints apply to list partitioned tables:

• The partition whose boundary value is DEFAULT is the default partition.
• Each list partitioned table can have only one DEFAULT partition.
• The number of partitions in a partitioned table cannot exceed 32767, and the number of boundary values of all partitions cannot exceed 32767.
• Regardless of the number of partition keys, the boundary of the DEFAULT partition can only be DEFAULT.
• If a partition key consists of multiple columns, each partition_value must contain the values of all partition keys. If a partition key contains only one column, the parentheses on both sides of partition_value can be omitted. For details, see Example: Creating a List Partitioned Table with Multiple Partition Keys.
• If the partition key consists of multiple columns, compare the values in the columns one by one. If a value is different from another value, regardless of their columns, they are different values.
• Each value of partition_value must be unique.
• When data is inserted, if its partition key and value falls into the boundary of a non-DEFAULT partition, the data is written to the partition. Otherwise, the data is written to the DEFAULT partition.

NULLS DISTINCT

Can be inserted repeatedly.

Can be inserted repeatedly.

NULLS NOT DISTINCT

Cannot be inserted repeatedly.

Cannot be inserted if the non-null values are equal. Can be inserted if the non-null values are not equal.

NULLS IGNORE

Can be inserted repeatedly.

Cannot be inserted if the non-null values are equal. Can be inserted if the non-null values are not equal.

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