Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ SQL Syntax/ SQL Statements/ C/ CREATE TABLE SUBPARTITION
Updated on 2025-10-23 GMT+08:00
View PDF
Share

CREATE TABLE SUBPARTITION

Description

Creates a level-2 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. For a level-2 partitioned table, the top-level node table and level-1 partitioned table are logical tables and do not store data. Only the level-2 partitioned (leaf node) stores data.

The partitioning scheme of a subpartitioned table is a combination of the partitioning schemes (described in CREATE TABLE PARTITION) of two partitions.

In M-compatible database mode, only four combination solutions for level-2 partitioned tables are supported: range-hash partitioning, range-key partitioning, list-hash partitioning, and list-key partitioning. Currently, level-2 partitioned tables can only be row-store tables.

Precautions

  • A level-2 partitioned table has two partition keys, and each partition key supports only one column and does not support expressions.
  • 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. If a local unique index is created, all partition keys must be included.
  • When a level-2 partitioned table is created, if the specified level-2 partition is not displayed under the level-1 partition, a level-2 partition with the same range is automatically created.
  • The number of level-2 partitions (leaf nodes) in a level-2 partitioned table cannot exceed 1048575. There is no limit on the number of level-1 partitions, but there must be at least one level-2 partition under a level-1 partition.
  • The maximum total number of partitions (including level-1 and level-2 partitions) in a level-2 partitioned table is 1048575. Generally, it is impossible for services to create so many partitions and the memory will be insufficient if so. Create partitions based on the value of local_syscache_threshold. The memory used by the level-2 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 2,000 and the number of subpartitions multiplied by (Number of local indexes + 1) be less than or equal to 10,000.
  • If the memory is insufficient due to too many partitions, the performance deteriorates sharply.
  • Level-2 partitioned tables support only row store.
  • Clusters are not supported.
  • When specifying a partition for query, for example, select * from tablename partition/subpartition (partitionname), ensure that the keywords partition and subpartition are correct. If they are incorrect, no error is reported during the query. In this case, the query is performed based on the table alias.
  • Encrypted databases and ledger databases are not supported.
  • In the PARTITION/SUBPARTITION FOR (VALUES) syntax for level-2 partitioned tables, VALUES can only be constants.
  • In the PARTITION/SUBPARTITION FOR (VALUES) syntax for level-2 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.
  • Currently, the statement specifying a partition cannot perform global index scan.

Syntax

CREATE TABLE [ IF NOT EXISTS ] subpartition_table_name
( 
    { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table
)
    [ table_option [ [ , ] ... ] ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    PARTITION BY { RANGE [ COLUMNS ] | LIST [ COLUMNS ] } (partition_key) [ PARTITIONS integer ] 
        SUBPARTITION BY { HASH | KEY} (subpartition_key) [ SUBPARTITIONS integer ]
    (
  PARTITION partition_name1 [ VALUES LESS THAN {(val1) | MAXVALUE} | VALUES [IN] (val1[, …]) ]
        [ TABLESPACE [=] tablespace ] 
        [(
           { SUBPARTITION subpartition_name1
            [ TABLESPACE [=] tablespace ] } [, ...]
        )] [partition_options] [, ...]
    );
  • table_option is as follows: 
    AUTO_INCREMENT [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | ENGINE [=] engine_name
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
}
    • If COMMENT is set for multiple times in the same place, only the last value takes effect.
    • If AUTO_INCREMENT is set for multiple times in the same place, only the last value takes effect.
    • If COLLATE is set for multiple times in the same place, only the last value takes effect.
    • When ENGINE and ROW_FORMAT are used, no error message is displayed. However, the settings do not take effect.
  • column_constraint is as follows: 
    [ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) |
  DEFAULT default_expr |
  ON UPDATE now_expr |
  [ GENERATED ALWAYS ] AS ( generation_expr ) [STORED | VIRTUAL] |
  UNIQUE [KEY] index_parameters |
  [ PRIMARY ] KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH SIMPLE ]
      [ ON DELETE action ] [ ON UPDATE action ] }
  • table_constraint is as follows: 
    [ CONSTRAINT [ constraint_name ] ]
{ CHECK ( expression ) |
  UNIQUE [ INDEX | KEY ] [ index_name ][ USING method ] ( { { column_name [ ( length ) ] | ( expression) } [ ASC | DESC ] } [, ... ] ) index_parameters [USING method| COMMENT 'string']|
  PRIMARY KEY [index_name][ USING method ] ( { column_name  }[ ASC | DESC ] } [, ... ] ) index_parameters [USING method| COMMENT 'string']|
  FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ (refcolumn [, ... ] ) ]
      [ MATCH FULL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ]
  }
  • index_parameters is as follows: 
    [ WITH ( {storage_parameter = value} [,...]
[ USING INDEX TABLESPACE tablespace_name ]
  • partition_options: 
    ENGINE [=] 'string' |
STORAGE ENGINE [=] 'string'

    Currently, no error is reported for the syntax, but the syntax does not take effect.

  • update_expr is as follows: 
    { CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }

Parameters

  • IF NOT EXISTS

    Sends a notice instead of an error if tables have identical names. The notice prompts that the table relationship already exists.

  • subpartition_table_name

    Specifies the name of a level-2 partitioned table.

    Value range: character strings complying with Identifier Description.

  • column_name

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

    Value range: character strings complying with Identifier Description.

  • data_type

    Specifies the data type of a 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. The utf8mb4_bin, utf8mb4_general_ci, utf8mb4_unicode_ci, binary, gbk_chinese_ci, gbk_bin, gb18030_chinese_ci, and gb18030_bin collations are supported. For details, see Table-level Character Sets and Collations.
    • Only the character type supports the specified character set. If the BINARY character set or collation is specified, the character type is converted to the corresponding binary type. If the type mapping does not exist, an error is reported. Currently, only the mapping from the TEXT type to the BLOB type is available.
    • Except the BINARY character set and collation, only the character set that is the same as the database encoding can be specified.
    • If the character set or collation of a column is not explicitly specified and the default character set or collation of the table is specified, the character set or collation of the column is inherited from the table.
  • 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.

    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 and can be applied to more than one column.
  • index_name

    Index name.

    • For a foreign key constraint, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
    • If both constraint_name and index_name are specified for the UNIQUE KEY constraint, the value of index_name will be used as the index name.
  • USING method

    Specifies the method of creating an index.

    For details about the value range, see "USING method" in Parameters.

    • Currently, only the B-tree index method is supported.
  • ASC | DESC

    ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

  • LIKE source_table

    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 the 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.

    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.

  • AUTO_INCREMENT [ = ] value

    This clause specifies an initial value for an auto-increment column. The value must be a positive integer and cannot exceed 2127 – 1.

  • 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'.

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

    Specifies an optional storage parameter for a table or an index. The description of parameters is as follows:

    • FILLFACTOR

      The fill factor of a table is a percentage from 10 to 100. If the Ustore is used, the default value is 92. If the Astore is used, the default value is 100 (completely filled). 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. Currently, an M-compatible database supports only row store.

      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 no table is specified, data is stored in in-place update mode by default.

    • COMPRESSION

      Row-store tables do not support compression.

    • segment

      The data is stored in segment-page mode. This parameter supports only row-store tables. Temporary tables and unlogged tables are not supported.

      Value range: on and off

      Default value: off

    • statistic_granularity

      Records the default PARTITION_MODE when the table analyzes statistics. This parameter is invalid for non-partitioned tables.

      Value range: See the options of PARTITION_MODE.

      Default value: AUTO.

      Table 1 PARTITION_MODE options

      PARTITION_MODE Option

      Description

      ALL

      Specifies whether to collect statistics about the entire table and level-1 partition.

      GLOBAL

      Specifies whether to collect statistics about the entire table.

      PARTITION

      Specifies whether to collect statistics about the level-1 partition.

      GLOBAL AND PARTITION

      Specifies whether to collect statistics about the entire table and level-1 partition.

      ALL COMPLETE

      Specifies whether to collect statistics about the entire table and level-1 partition.

      AUTO

      Default value.
  • TABLESPACE tablespace_name

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

    Note: You need to create or delete tablespaces in a non-M-compatible database.

  • PARTITION BY {RANGE [COLUMNS] | LIST [COLUMNS]} (partition_key)
    • For partition_key, the partition key of the partitioning policy supports only one column and does not support expressions.
    • The data types supported by the partition key are the same as those supported by the level-1 partitioned table.
    • The COLUMNS keyword can be added only after RANGE or LIST. The semantics of RANGE COLUMNS is the same as that of RANGE, and the semantics of LIST COLUMNS is the same as that of LIST.
    • The meaning of KEY is the same as that of HASH.
  • SUBPARTITION BY {HASH | KEY} (subpartition_key)
    • For subpartition_key, the partition key of the partitioning policy supports only one column and does not support expressions.
    • The data types supported by the partition key are the same as those supported by the level-1 partitioned table.
  • 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 RANGE and LIST partitions.
    • 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.
  • SUBPARTITIONS integer

    Specifies the number of level-2 partitions.

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

    • This clause can be specified only for level-2 HASH and KEY partitions.
      • If level-2 partitions are not defined, an integer number of level-2 partitions are automatically generated in each level-1 partition. The automatically generated level-2 partitions are named in the format of "level-1 partition name+sp+number", where the number ranges from 0 to integer minus 1. The tablespace of the partition is the tablespace of the table by default.
      • If level-2 partitions are defined, the number of level-2 partitions must be the same as the value of integer.
      • If level-2 partitions are not defined and the number of level-2 partitions is not specified, a unique level-2 partition is created.
  • NOT NULL

    Specifies that the column value cannot be NULL. ENABLE can be omitted.

  • NULL

    Specifies that the column value can be NULL, which is the default value.

    This clause is only provided for compatibility with non-standard SQL databases. It is not recommended.

  • CHECK ( expression )

    Specifies an expression producing a Boolean result where the INSERT or UPDATE operation of new or updated rows can succeed only when the expression result is true or unknown; otherwise, an error is thrown and the database is not altered.

    • <>NULL and !=NULL are invalid to expressions and need to be rewritten as is NOT NULL.
    • The comparison results of floating-point numbers may be inconsistent between the tables with the CHECK constraint created when the precision transfer function is disabled (m_format_behavior_compat_options is not set to enable_precision_decimal) and the tables with the CHECK constraint created after the precision transfer function is enabled. If you want to use a table with the CHECK constraint created when the precision transfer function is not enabled while the function is now enabled, you are advised to use the ALTER TABLE syntax to redefine the CHECK constraint after enabling the precision transfer function. 
      m_db=# SET m_format_behavior_compat_options='';
SET
m_db=# CREATE TABLE mm1(a float(10, 4), b float(5, 3), CHECK(a/b=1.7142858) STORED);
CREATE TABLE
m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
ERROR:  New row in relation "mm1" violates check constraint "mm1_check".
DETAIL:  N/A
m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
SET
m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
INSERT 0 1
m_db=# CREATE TABLE mm2(a float(10, 4), b floaT(5, 3), CHECK(a/b=1.7142858) STORED);
CREATE TABLE
m_db=# INSERT INTO mm2 VALUES(1.2, 0.7);
ERROR:  New row in relation "mm2" violates check constraint "mm2_check".
DETAIL:  N/A

m_db=# DROP TABLE mm1, mm2;
CREATE TABLE
  • DEFAULT default_expr
    • The DEFAULT clause is used to specify a default expression for a 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.
    • When the default expression is not enclosed in parentheses, the following content can be specified: constants, numeric constants with positive and negative signs, and update_expr.
    • When the default expression is enclosed in parentheses, the following content can be specified: constants, numeric constants with positive and negative signs, update_expr, CURRENT_TIME/CURTIME functions, and CURRENT_DATE/CURDATE functions. (CURRENT_TIME/CURRENT_DATE can be called without parentheses.)
    • The value of update_expr can be used as the default value only for columns of the TIMESTAMP or DATETIME type, and the precision of the columns must be the same as that of update_expr.
  • ON UPDATE update_expr

    The ON UPDATE clause is an attribute constraint of a column.

    When an UPDATE operation is performed on a tuple in a table, if new values of updated columns are different from old values in the table, column values with this attribute but not in updated columns are automatically updated to the current timestamp. If new values of updated columns are the same as old values in the table, column values with this attribute but not in updated columns remain unchanged. If columns with this attribute are in updated columns, column values are updated according to the specified update value.

    • In terms of syntax, update_expr supports four keywords: CURRENT_TIMESTAMP, LOCALTIMESTAMP, LOCALTIME, and NOW(). You can also specify or not specify the precision of a keyword with parentheses. For example, ON UPDATE CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP(5), ON UPDATE LOCALTIMESTAMP(), and ON UPDATE LOCALTIMESTAMP(6). If the keyword does not contain parentheses or contains empty parentheses, the precision is 0. The NOW keyword cannot contain parentheses. The four types of keywords are synonyms of each other and have the same attribute effect.
    • This attribute can be specified on columns of the TIMESTAMP and DATETIME types.
    • The precision specified by this attribute must be the same as that specified by the type in the corresponding column. Otherwise, an error is reported. For example, CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6));. If the precision is inconsistent, ERROR: Invalid ON UPDATE clause for "col1" is reported.
    • The same column cannot be specified for this attribute and the generated column constraint at the same time.
    • This attribute cannot be specified for the partition key in a partitioned table.
  • [GENERATED ALWAYS] AS ( generation_expr ) [STORED | VIRTUAL]

    Creates a column as a generated column. You can specify the STORED and VIRTUAL keywords to indicate the column value storage mode of the generated column.

    • STORED: creates a stored generated column. The column value needs to be stored. When a tuple is inserted or updated, the column value is calculated and stored by a generated column expression.
    • VIRTUAL: creates a virtual generated column. The column value does not need to be stored. If a query statement involves a virtual generated column, the column value is calculated by a generated column expression.
    • The keywords STORED and VIRTUAL can be omitted. The default value is STORED. When m_format_dev_version is set to s2, the default value is VIRTUAL.
    • The generated expression cannot reference data other than the current row in any way. A generated expression can reference generated columns that are defined earlier than the current generated column, but cannot reference system columns. Generated column expressions cannot reference system variables, user-defined variables, or variables in stored procedures. Generated column expressions cannot reference auto-increment columns, and generated columns do not support the definition of auto-increment column attributes. Generated expressions cannot return result sets or use subqueries, aggregate functions, window functions, or user-defined functions. Functions called by generated expressions can only be immutable functions.
    • Default values cannot be specified for generated columns.
    • A stored generated column can be used as a part of a partition key. In this case, ALTER TABLE cannot be used to modify a stored generated column.
    • A virtual generated column cannot be used as part of a partition key.
    • A virtual generated column cannot be referenced by a foreign key constraint.
    • Indexes cannot be created on virtual generated columns.
    • A stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.
    • When m_format_dev_version is set to s2, the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.
    • The method of modifying and deleting generated columns is the same as that of ordinary columns. If you delete a base column that a generated column depends on, the generated column is automatically deleted. When m_format_dev_version is set to s2, you need to delete the corresponding generated column before deleting the base column on which the generated column depends.
    • Data cannot be written directly to a generated column. In the INSERT or UPDATE statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified. When inserting generated columns to a view that can be updated, do not specify values for generated columns. However, you can specify the keyword DEFAULT.
    • When a stored generated column does not meet its CHECK constraint and m_format_dev_version is set to s2, the ALTER TABLE statement fails to be executed to change the stored generated column and an error is reported.
    • The permission control for generated columns is the same as that for ordinary columns.
    • The comparison results of floating-point numbers may be inconsistent between the tables with columns generated when the precision transfer function is disabled (m_format_behavior_compat_options is not set to enable_precision_decimal) and the tables with columns generated after the precision transfer function is enabled. If you want to use a table with columns generated when the precision transfer function is not enabled while the function is now enabled, you are advised to use the ALTER TABLE syntax to redefine the generated columns after enabling the precision transfer function. 
      m_db=# SET m_format_behavior_compat_options='';
SET
m_db=# CREATE TABLE mm1(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED);
CREATE TABLE
m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
INSERT 0 1
m_db=# SELECT * FROM mm1;
   a    |   b   | c 
--------+-------+---
 1.2000 | 0.700 | 0
(1 row)

m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
SET
m_db=# CREATE TABLE mm2(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED);
CREATE TABLE
m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
INSERT 0 1
m_db=# INSERT INTO mm2 VALUES(1.2, 0.7);
INSERT 0 1
m_db=# SELECT * FROM mm1;
   a    |   b   | c 
--------+-------+---
 1.2000 | 0.700 | 0
 1.2000 | 0.700 | 1
(2 rows)

m_db=# SELECT * FROM mm2;
   a    |   b   | c 
--------+-------+---
 1.2000 | 0.700 | 0
(1 row)

m_db=# DROP TABLE mm1, mm2;
DROP TABLE
  • AUTO_INCREMENT

    Specifies an auto-increment column.

    For details, see •AUTO_INCREMENT.

  • Column-level UNIQUE constraint: UNIQUE [KEY] index_parameters

    Specifies that the designated column or columns must have unique values across the entire table.

    UNIQUE KEY has the same semantics as UNIQUE.

  • Table-level UNIQUE constraint: UNIQUE [INDEX | KEY][ index_name ][ USING method ]( {{ column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters

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

    For the purpose of a UNIQUE constraint, NULL is not considered equal.

    column_name (length) indicates the prefix key. For details, see •column_name ( length ).

    index_name indicates the index name.

    If both constraint_name and index_name are specified for the UNIQUE KEY constraint, the value of index_name will be used as the index name.

  • Column-level PRIMARY KEY constraint: [PRIMARY] KEY index_parameters

    Table-level PRIMARY KEY constraint: PRIMARY KEY [index_name] [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters

    Specifies that one or more columns in the table require unique and non-NULL values.

    Only one primary key can be specified for a table.

Examples

  • Example 1: Create level-2 partitioned tables of various combination types. 
     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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
    		 
    m_db=# CREATE TABLE list_hash
(
    month_code VARCHAR ( 30 ) NOT NULL ,
    dept_code  VARCHAR ( 30 ) NOT NULL ,
    user_no    VARCHAR ( 30 ) NOT NULL ,
    sales_amt  int
)
PARTITION BY LIST (month_code) SUBPARTITION BY HASH (dept_code)
(
  PARTITION p_201901 VALUES ( '201902' )
  (
    SUBPARTITION p_201901_a,
    SUBPARTITION p_201901_b
  ),
  PARTITION p_201902 VALUES ( '201903' )
  (
    SUBPARTITION p_201902_a,
    SUBPARTITION p_201902_b
  )
);
m_db=# INSERT INTO list_hash VALUES('201902', '1', '1', 1);
m_db=# INSERT INTO list_hash VALUES('201902', '2', '1', 1);
m_db=# INSERT INTO list_hash VALUES('201902', '3', '1', 1);
m_db=# INSERT INTO list_hash VALUES('201903', '4', '1', 1);
m_db=# INSERT INTO list_hash VALUES('201903', '5', '1', 1);
m_db=# INSERT INTO list_hash VALUES('201903', '6', '1', 1);
m_db=# SELECT * from list_hash;
 month_code | dept_code | user_no | sales_amt 
------------+-----------+---------+-----------
 201903     | 4         | 1       |         1
 201903     | 5         | 1       |         1
 201903     | 6         | 1       |         1
 201902     | 2         | 1       |         1
 201902     | 3         | 1       |         1
 201902     | 1         | 1       |         1
(6 rows)

m_db=# DROP TABLE list_hash;
m_db=# CREATE TABLE range_hash
(
    month_code VARCHAR ( 30 ) NOT NULL ,
    dept_code  VARCHAR ( 30 ) NOT NULL ,
    user_no    VARCHAR ( 30 ) NOT NULL ,
    sales_amt  int
)
PARTITION BY RANGE (month_code) SUBPARTITION BY HASH (dept_code)
(
  PARTITION p_201901 VALUES LESS THAN( '201903' )
  (
    SUBPARTITION p_201901_a,
    SUBPARTITION p_201901_b
  ),
  PARTITION p_201902 VALUES LESS THAN( '201904' )
  (
    SUBPARTITION p_201902_a,
    SUBPARTITION p_201902_b
  )
);
m_db=# INSERT INTO range_hash VALUES('201902', '1', '1', 1);
m_db=# INSERT INTO range_hash VALUES('201902', '2', '1', 1);
m_db=# INSERT INTO range_hash VALUES('201902', '1', '1', 1);
m_db=# INSERT INTO range_hash VALUES('201903', '2', '1', 1);
m_db=# INSERT INTO range_hash VALUES('201903', '1', '1', 1);
m_db=# INSERT INTO range_hash VALUES('201903', '2', '1', 1);
m_db=# SELECT * from range_hash;
 month_code | dept_code | user_no | sales_amt 
------------+-----------+---------+-----------
 201902     | 2         | 1       |         1
 201902     | 1         | 1       |         1
 201902     | 1         | 1       |         1
 201903     | 2         | 1       |         1
 201903     | 2         | 1       |         1
 201903     | 1         | 1       |         1
(6 rows)

m_db=# DROP TABLE range_hash;
  • Example 2: Specify partitions in a level-2 partitioned table using DML. 
    m_db=# CREATE TABLE range_hash
(
    month_code VARCHAR ( 30 ) NOT NULL ,
    dept_code  VARCHAR ( 30 ) NOT NULL ,
    user_no    VARCHAR ( 30 ) NOT NULL ,
    sales_amt  int
)
PARTITION BY RANGE (month_code) SUBPARTITION BY HASH (dept_code)
(
  PARTITION p_201901 VALUES LESS THAN( '201903' )
  (
    SUBPARTITION p_201901_a,
    SUBPARTITION p_201901_b
  ),
  PARTITION p_201902 VALUES LESS THAN( '201904' )
  (
    SUBPARTITION p_201902_a,
    SUBPARTITION p_201902_b
  )
);
-- Insert data to a specified level-1 partition.
m_db=# INSERT INTO range_hash partition (p_201901) VALUES('201902', '1', '1', 1);
-- The actual partition is inconsistent with the specified partition. An error is reported.
m_db=# INSERT INTO range_hash partition (p_201902) VALUES('201902', '1', '1', 1);
ERROR:  inserted partition key does not map to the table partition
DETAIL:  N/A.

-- Query data in a specified partition.
m_db=# SELECT * from range_hash partition (p_201901);
 month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
 201902     | 1         | 1       |         1
(1 row)

m_db=# SELECT * from range_hash partition for ('201902');
 month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
 201902     | 1         | 1       |         1
( rows)

-- Update data in a specified partition.
m_db=# UPDATE range_hash partition (p_201901) SET user_no = '2';
m_db=# SELECT * from range_hash;
 month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
 201902     | 1         | 2       |         1
(1 row)
m_db=# UPDATE range_hash partition for ('201902') SET user_no = '4';
m_db=# SELECT * from range_hash;
 month_code | dept_code | user_no | sales_amt
------------+-----------+---------+-----------
 201902     | 1         | 4       |         1
(1 row)

-- Delete data from a specified partition.
m_db=# DELETE FROM range_hash partition (p_201901);
DELETE 1
Parent topic: C