CREATE TABLE PARTITION

Description

Creates a partitioned table. Partitioning refers to splitting what is logically one large table into smaller physical pieces based on specific schemes. The table based on the logic is called a partitioned table, and each physical piece is called a partition. Data is stored on these physical partitions, instead of the logical partitioned table.

The common forms of partitioning include range partitioning, interval partitioning, hash partitioning, list partitioning, and value partitioning. Currently, row-store tables support range partitioning, hash/key partitioning, and list partitioning.

In range partitioning, a table is partitioned based on ranges defined by one or more columns, with no overlap between the ranges of values assigned to different partitions. Each range has a dedicated partition for data storage.

The partitioning policy for range partitioning refers to how data is inserted into partitions.

In range partitioning, a table is partitioned based on partition key values. If a record can be mapped to a partition, it is inserted into the partition; if it cannot, an error message is returned. Range partitioning is the most commonly used partitioning policy.

In hash partitioning, a modulus and a remainder are specified for each partition based on a column in the table, and records to be inserted into the table are allocated to the corresponding partition, the rows in each partition must meet the following condition: The value of the partition key divided by the specified modulus generates the remainder specified for the partition key.

In hash partitioning, table is partitioned based on partition key values. If a record can be mapped to a partition, it is inserted into the partition; if it cannot, an error message is returned.

List partitioning is to allocate the records to be inserted into a table to the corresponding partition based on the key values in each partition. The key values do not overlap in different partitions. A partition is created for each group of key values to store corresponding data.

In list partitioning, table is partitioned based on partition key values. If a record can be mapped to a partition, it is inserted into the partition; if it cannot, an error message is returned.

Partitioning can provide several benefits:

• Query performance can be improved drastically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning narrows the range of data search and improves data access efficiency.
• In the case of an INSERT or UPDATE operation on most portions of a single partition, performance can be improved by taking advantage of continuous scan of that partition instead of partitions scattered across the whole table.
• Frequent loading or deletion operations on records in a separate partition can be accomplished by reading or deleting that partition. This not only improves performance but also avoids the VACUUM overload caused by bulk DELETE operations (hash partitions cannot be deleted).

Precautions

• If the constraint key of the unique constraint and primary key constraint contains all partition keys, a local index is created for the constraints. Otherwise, a global index is created.
• Currently, hash partitioning supports only single-column partition keys, and does not support multi-column partition keys.
• In the PARTITION FOR (values) syntax for partitioned tables, values can only be constants.
• In the PARTITION FOR (values) syntax for partitioned tables, if data type conversion is required for values, you are advised to use forcible type conversion to prevent the implicit type conversion result from being inconsistent with the expected result.
• The maximum number of partitions is 104,8575. Generally, it is impossible to create so many partitions, because too many partitions may cause insufficient memory. Create partitions based on the value of local_syscache_threshold. The memory used by the partitioned tables is about (number of partitions x 3/1024) MB. Theoretically, the memory occupied by the partitions cannot be greater than the value of local_syscache_threshold. In addition, some space must be reserved for other functions.
• Considering the impact on performance, it is recommended that the maximum number of partitions in a single table be less than or equal to 2000 and the number of subpartitions multiplied by (Number of local indexes + 1) be less than or equal to 10000.
• If the memory is insufficient due to too many partitions, the performance deteriorates sharply.
• Currently, the statement specifying a partition cannot perform global index scan.
• Data of the XML type cannot be used as partition keys or level-2 partition keys.
• When UPDATE or DELETE is performed on a partitioned table, if the generated plan is not an FQS or Stream plan, the statement execution efficiency is low. You are advised to check statements and eliminate factors that cannot be pushed down to generate FQS or Stream plans.

• If you add a row-level expression when adding or changing an ILM policy for a data object, note that the row-level expression supports only the functions listed in the whitelist. For details about the whitelist function list, see Row Expression Function Whitelist.

Syntax

 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
26

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} [, ... ] ) ]
    [ COMPRESS | NOCOMPRESS ]
    [ 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 [, ...] ) { 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 is as follows:

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

• column_constraint is as follows:

  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 is as follows:

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

• like_option is as follows:

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

• index_parameters is as follows:

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

Parameters

• IF NOT EXISTS

  Sends a notice instead of throwing an error, if a table with the same name exists.

• partition_table_name

  Specifies the name of a partitioned table.

  Value range: a string. It must comply with the naming convention.

• column_name

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

  Value range: a string. It must comply with the naming convention.

• data_type

  Specifies the data type of the column.

• COLLATE collation

  Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the SELECT * FROM pg_collation command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.

• CONSTRAINT constraint_name

  Specifies the name of 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. You can run the SELECT * FROM pg_collation command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.

  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 a particular column but can apply to more than one column.
• 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.

  The new table and original table are decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table.

  • Default expressions for the copied column definitions will be copied only if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having default values NULL.
  • Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. These rules also apply to column constraints and table constraints.
  The copied columns and constraints are not merged with similarly named columns and constraints. If the same name is specified explicitly or in another LIKE clause, an error is reported.

  • If INCLUDING UPDATE is specified, the ON UPDATE CURRENT_TIMESTAMP attribute of the original table column is copied to the new table column. By default, this attribute is not copied.

  • Any indexes on the original table will not be created on the new table, unless the INCLUDING INDEXES clause is specified.
  • If INCLUDING STORAGE is specified, the STORAGE setting of the original table column is also copied. By default, the STORAGE setting is not included.
  • If INCLUDING COMMENTS is specified, comments of the original table columns, constraints, and indexes are also copied. The default behavior is to exclude comments.
  • If INCLUDING RELOPTIONS is specified, the storage parameters (WITH clauses) of the original table are also copied to the new table. The default behavior is to exclude partition definition of the storage parameter of the original table.
  • If INCLUDING DISTRIBUTION is specified, the new table will copy the distribution information of the original table, including distribution type and key, and the new table cannot use the DISTRIBUTE BY clause. The default behavior is to exclude distribution information of the original table.
  • INCLUDING ALL contains the meaning of INCLUDING DEFAULTS, INCLUDING UPDATE, INCLUDING CONSTRAINTS, INCLUDING INDEXES, INCLUDING STORAGE, INCLUDING COMMENTS, INCLUDING RELOPTIONS, and INCLUDING DISTRIBUTION.

  

  • The CREATE TABLE table_name LIKE source_table syntax is supported only when sql_compatibility is set to 'MYSQL' (MySQL-compatible database), b_format_version is set to 5.7, and b_format_dev_version is set to s2.
  • In a MySQL-compatible database, if b_format_version is set to 5.7 and b_format_dev_version is set to s2, INCLUDING and EXCLUDING cannot be specified. In this case, it is equivalent to specify INCLUDING ALL by default.
• AUTO_INCREMENT [ = ] value
  This clause specifies an initial value for an auto-increment column. The value must be a positive number and cannot exceed 2¹²⁷ – 1.

  

  This clause takes effect only when sql_compatibility is set to 'MYSQL'.

• COMMENT [ = ] 'string'
  • The COMMENT [ = ] 'string' clause is used to add comments to a table.
  • The COMMENT 'string' in column_constraint indicates that comments are added to a column.
  • The COMMENT 'string' in table_constraint indicates that comments are added to the indexes corresponding to the primary key and unique key.

  For details, see COMMENT [ = ] 'string'.

• CHARACTER SET | CHARSET charset

  Specifies the character set of a table column. If this parameter is specified separately, the collation of the table column is set to the default collation of the specified character set.

  This syntax is supported only in a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL').

• COLLATE collation

  Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the SELECT * FROM pg_collation command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result. In a MySQL-compatible database (that is, sql_compatibility set to 'MYSQL'), utf8mb4_bin, utf8mb4_general_ci, utf8mb4_unicode_ci, binary, gbk_chinese_ci, gbk_bin, gb18030_chinese_ci, and gb18030_bin are also supported.

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

  Specifies an optional storage parameter for a table or an index. Optional parameters are as follows:

  • FILLFACTOR

    The fill factor of a table is a number from 10 to 100. 100 (complete filling) is the default value. When a smaller fill factor is specified, INSERT operations fill 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 entries are never updated, setting the fill factor to 100 (complete filling) is the best choice, but in heavily updated tables a smaller fill factor would be appropriate.

    Value range: 10–100

  • ORIENTATION

    Determines the data storage mode of the table.

    Value range:

    • ROW (default value): The data will be stored in rows.
      

      orientation cannot be modified.

  • STORAGE_TYPE

    Specifies the storage engine type. This parameter cannot be modified once it is set.

    Value range:

    • USTORE indicates that tables support the in-place update storage engine. Note that the track_counts and track_activities parameters must be enabled when the Ustore table is used. Otherwise, space bloating may occur.
    • ASTORE indicates that tables support the append-only storage engine.

    Default value:

    If this parameter is not specified, the enable_default_ustore_table parameter determines the storage engine mode. The default value is append-only storage.

  • COMPRESSION
    • Row-store tables do not support compression.
  • statistic_granularity

    Specifies the default value of partition_mode of a table during statistics analysis. For details about partition_mode, see ANALYZE|ANALYSE. This parameter is invalid for non-partitioned tables.

    Value range: See the value range of partition_mode.

    Default value: AUTO.

  • enable_tde

    Specifies that the table is an encrypted table. The database automatically encrypts the data in the encryption table before storing it. Before using this parameter, ensure that TDE function has been enabled using the GUC parameter enable_tde and the information for accessing the key service has been set using the GUC parameter tde_key_info. For details about how to use this parameter, see section "Transparent Data Encryption" in Feature Guide. This parameter applies only to row-store tables, segment-page tables, hash bucket tables, temporary tables, and unlogged tables.

    Value range: on and off When enable_tde is set to on, key_type, tde_cmk_id, and dek_cipher are automatically generated by the database and cannot be manually specified or modified.

    Default value: off

  • encrypt_algo

    Specifies the encryption algorithm of the encryption table. This parameter must be used together with enable_tde.

    Value range: a string. The value can be AES_128_CTR or SM4_CTR.

    Default value: null if enable_tde is not set, or AES_128_CTR if enable_tde is set.

  • dek_cipher

    Specifies the DEK ciphertext. After a user sets the enable_tde parameter for a table, the database automatically generates a data key.

    Value range: a string

    Default value: null

  • key_type

    Specifies the type of the master key. After the enable_tde parameter is set for a table, the database automatically obtains the master key type from the GUC parameter tde_key_info.

    Value range: a string

    Default value: null

  • cmk_id

    Specifies the ID of the master key. After the enable_tde parameter is set for a table, the database automatically obtains the master key ID from the GUC parameter tde_key_info.

    Value range: a string

    Default value: null

  • hashbucket

    Creates a hash table that uses buckets. This parameter supports only row-store tables, including row-store range tables.

    Value range: on and off

    Default value: off

    

    In current version, DDL operations on hash bucket tables are affected. Therefore, you are advised not to frequently perform DDL operations on hash bucket tables.

• COMPRESS / NOCOMPRESS

  Specifies keyword COMPRESS during the creation of a table, so that the compression feature is triggered in case of BULK INSERT operations. 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, that is, tuple data is not compressed before storage. Row-store tables do not support compression.

• TABLESPACE tablespace_name

  Specifies that the new table will be created in the tablespace_name tablespace. If not specified, the default tablespace is used.

• DISTRIBUTE BY

  Specifies how the table is distributed or replicated between nodes.

  For the value range and details, see DISTRIBUTE BY.

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

  TO GROUP specifies the node group to which the table to be created belongs. TO NODE is used for internal scale-out tools.

• PARTITION BY RANGE [COLUMNS] (partition_key)

  Creates a range partition. partition_key is the name of the partition key.

  The COLUMNS keyword can be used only when sql_compatibility is set to 'MYSQL'. The semantics of PARTITION BY RANGE COLUMNS is the same as that of PARTITION BY RANGE.

  (1) Assume that the VALUES LESS THAN syntax is used.

  

  If the VALUE LESS THAN clause is used, a range partitioning policy supports a partition key with up to 16 columns.

  Data types supported by the partition keys are as follows: TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, FLOAT4, FLOAT8, 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.

  (2) Assume that the START END syntax is used.

  

  In this case, only one partition key is supported.

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

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

  Specifies the information of partitions. partition_name is the name of a range partition. partition_value is the upper limit of a range partition, and the value depends on the type of partition_key. MAXVALUE usually specifies the upper limit of the last range partition.

  

  • Each partition requires an upper limit.
  • The data type of the upper limit must be the same as that of the partition key.
  • In a partition list, partitions are arranged in ascending order of upper limits. A partition with a smaller upper limit value is placed before another partition with a larger one.
  • MAVALUE that is not in parentheses can be used only when sql_compatibility is set to 'MYSQL' and can have only one partition key.
• 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)}

  Specifies the information of partitions.

  • 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 value or end 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 usually specifies the upper limit of the last range partition.
  

  1. 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.
  2. The START END syntax must comply with the following rules:
     • The value of START (if any, same for the following situations) in each partition_start_end_item must be smaller than that of END.
     • In two adjacent partition_start_end_item statements, the value of the first END must be equal to that of the second START.
     • The value of EVERY in each partition_start_end_item must be a positive number (in ascending order) and must be smaller than END minus START.
     • Each partition includes the start value (unless it is MINVALUE) and excludes the end value. The format is as follows: [START, END).
     • Partitions created by the same partition_start_end_item belong to the same tablespace.
     • If partition_name is a name prefix of a partition, the length must not exceed 57 bytes. If there are more than 57 bytes, the prefix will be automatically truncated.
     • When creating or modifying a partitioned table, ensure that the total number of partitions in the table does not exceed the maximum value 1048575.
  3. In statements for creating partitioned tables, START END and LESS THAN cannot be used together.
  4. The START END syntax in a partitioned table creation SQL statement will be replaced by the VALUES LESS THAN syntax when gs_dump is executed.
• PARTITION BY LIST [COLUMNS] (partition_key)

  Create a list partition. partition_key is the name of the partition key.

  The COLUMNS keyword can be used only when sql_compatibility is set to 'MYSQL'. The semantics of PARTITION BY LIST COLUMNS is the same as that of PARTITION BY LIST.

  • A list partitioning policy supports a partition key with up to 16 columns.
  • For the clause syntax VALUES [IN] (list_values), if list_values contains the key values of the corresponding partition, it is recommended that the number of key values of each partition be less than or equal to 64.
  • The clause VALUES IN can be used only when sql_compatibility is set to 'MYSQL'. The semantics is the same as that of VALUES.

  Partition keys support the following data types: TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC, VARCHAR(n), CHAR, BPCHAR, NVARCHAR2, TIMESTAMP[(p)] [WITHOUT TIME ZONE], TIMESTAMP[(p)] [WITH TIME ZONE], and DATE. The number of partitions cannot exceed 1048575.

• PARTITION BY HASH(partition_key)

  Create a hash partition. partition_key is the name of the partition key.

  For partition_key, the hash partitioning policy supports only one column of partition keys.

  Partition keys support the following data types: TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC, VARCHAR(n), CHAR, BPCHAR, TEXT, NVARCHAR2, TIMESTAMP[(p)] [WITHOUT TIME ZONE], TIMESTAMP[(p)] [WITH TIME ZONE], and DATE. The number of partitions cannot exceed 1048575.

• PARTITIONS integer

  Specifies the number of partitions.

  integer indicates the number of partitions. The value must be an integer greater than 0 and cannot be greater than 1048575.

  • When this clause is specified after the range and list partitions, each partition must be explicitly defined, and the number of defined partitions must be equal to the integer value. This clause can be specified after the range and list partitions only when sql_compatibility is set to 'MYSQL'.
  • When this clause is specified after the hash and key partitions, if the definition of each partition is not listed, an integer number of partitions are automatically generated. The automatically generated partition name is "p+number", and the number ranges from 0 to integer minus 1. The tablespace of the partition is the tablespace of the table by default. If each partition definition is explicitly defined, the number of defined partitions must be the same as the value of integer. If neither the partition definition nor the number of partitions is specified, a unique partition is created.
• { ENABLE | DISABLE } ROW MOVEMENT

  Sets row movement.

  If the tuple value is updated on the partition key during the UPDATE operation, the partition where the tuple is located is altered. Setting this parameter enables error messages to be reported or movement of the tuple between partitions.

  Value range:

  • ENABLE: Row movement is enabled.
  • DISABLE (default value): Row movement is disabled.

  If the row movement is enabled, an error may be reported when UPDATE and DELETE operations are performed concurrently. The causes are as follows:

  The old data is marked as deleted in the UPDATE and DELETE operations. If the row movement is enabled, the cross-partition update occurs when the partition key is updated. The kernel marks the old data in the old partition as deleted and adds a data to the new partition. As a result, the new data cannot be found by querying the old data.

  If data in the same row is concurrently operated, the cross-partition and non-cross-partition data results have different behaviors in the following three concurrency scenarios: UPDATE and UPDATE concurrency, DELETE and DELETE concurrency, as well as UPDATE and DELETE concurrency.

  1. For non-cross-partition data, no error is reported for the second operation after the first operation is performed.

     If the first operation is UPDATE, the latest data can be found and operated after the second operation is performed.

     If the first operation is DELETE, the second operation is terminated if the current data is deleted and the latest data cannot be found.

  2. For the cross-partition data result, an error is reported for the second operation after the first operation is performed.

     If the first operation is UPDATE, the second operation cannot find the latest data because the new data is in the new partition. Therefore, the second operation fails and an error is reported.

     If the first operation is DELETE, performing the second operation can find that the current data is deleted and the latest data cannot be found, but cannot determine whether the operation of deleting the old data is UPDATE or DELETE. If the operation is UPDATE, an error is reported. If the operation is DELETE, the operation is terminated. To ensure the data correctness, an error is reported.

  If the UPDATE and UPDATE concurrency, and UPDATE and DELETE concurrency are performed, the error can be solved only when the operations are performed serially. If the DELETE and DELETE concurrency are performed, the error can be solved by disabling the row movement.

Range Partitioning

• VALUES LESS THAN

  -- Create a tablespace.
  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';
  
  -- Create a partitioned table 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
  );
  
  -- Insert 1000 data records.
  INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');
  
  -- Check that the number of rows in the p1 partition is 199, that is, the record range is [1, 200).
  SELECT COUNT(*) FROM test_range1 PARTITION (p1);
   count 
  -------
     199
  (1 row)
  
  -- Check that the number of rows in the p2 partition is 200, that is, the record range is [200, 400).
  SELECT COUNT(*) FROM test_range1 PARTITION (p2);
   count 
  -------
     200
  (1 row)
  
  -- View the partition information.
  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)
  
  -- Delete.
  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 a partitioned table.
  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)
  );
  
  -- View the partition information.
  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)
  
  -- Delete.
  DROP TABLE test_range2;

List Partitioning

-- Create a list partitioned table.
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 data.
INSERT INTO test_list VALUES ('bob', 'Shanghai'),('scott', 'Sichuan');

-- Query partition data.
SELECT * FROM test_list PARTITION (p2);
 name |   area   
------+----------
 bob  | Shanghai
(1 row)
SELECT * FROM test_list PARTITION (pdefault);
 name  |  area   
-------+---------
 scott | Sichuan
(1 row)

-- Delete.
DROP TABLE test_list;

Hash Partitioning

-- Create a hash partitioned table and specify the number of partitions.
CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;

-- Create a hash partitioned table and specify the names of partitions.
CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(C1)(
    PARTITION pa,
    PARTITION pb,
    PARTITION pc
);

-- View the partition information.
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)

-- Delete.
DROP TABLE test_hash1,test_hash2;

Helpful Links

ALTER TABLE PARTITION and DROP TABLE