CREATE TABLE PARTITION

Function

CREATE TABLE PARTITION 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, hash partitioning, list partitioning, and value partitioning. Currently, row-store and column-store tables support only range 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. Currently, range partitioning only allows the use of the range partitioning policy.

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.

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 removing that partition. It also avoids the VACUUM overload caused by bulk DELETE operations.

Precautions

• When specifying a partition for query, for example, select * from tablename partition (partitionname), ensure that the keyword partition is correct. If it is incorrect, no error is reported during the query. In this case, the query is performed based on the table alias.
• In distributed mode, you can only use SELECT to specify partitions. If other syntax is used to specify partitions, an error is reported and no alias conversion is performed.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE TABLE [ IF NOT EXISTS ] partition_table_name
( [ 
    { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option [...] ] }
    [, ... ]
] )
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ COMPRESS | NOCOMPRESS ]
    [ TABLESPACE tablespace_name ]
    [ DISTRIBUTE BY { REPLICATION | { [ HASH ] ( column_name ) } } ]
    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ]
    PARTITION BY { 
        {RANGE (partition_key) ( partition_less_than_item [, ... ] )} |
        {RANGE (partition_key) ( partition_start_end_item [, ... ] )}
    } [ { ENABLE | DISABLE } ROW MOVEMENT ]; 

• column_constraint is as follows:

  1 2 3 4 5 6 7 8 9 10

  [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | UNIQUE [ index_parameters ] | PRIMARY KEY [ index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ 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 |
    FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
      
  [ DEFERRABLE | NOT DEFERRABLE ][ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

• like_option is as follows:

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

• index_parameters is as follows:

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

Parameter Description

• IF NOT EXISTS

  Sends a notice, but does not throw 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.

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

  Unlike INHERITS, 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.

  Unlike those of INHERITS, columns and constraints copied by LIKE 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.

  • Any indexes on the original table will not be created on the new table, unless the INCLUDING INDEXES clause is specified.
  • STORAGE settings for the copied column definitions are copied only if INCLUDING STORAGE is specified. The default behavior is to exclude STORAGE settings.
  • If INCLUDING COMMENTS is specified, comments for the copied columns, constraints, and indexes are copied. The default behavior is to exclude comments.
  • If INCLUDING RELOPTIONS is specified, the new table will copy the storage parameter (that is, WITH clause) of the source table. The default behavior is to exclude partition definition of the storage parameter of the source table.
  • If INCLUDING DISTRIBUTION is specified, the new table will copy the distribution information of the source table, including distribution type and key, and the new table cannot use DISTRIBUTE BY clause. The default behavior is to exclude distribution information of the original table.
  • INCLUDING ALL contains the meaning of INCLUDING DEFAULTS, INCLUDING CONSTRAINTS, INCLUDING INDEXES, INCLUDING STORAGE, INCLUDING COMMENTS, INCLUDING RELOPTIONS, and INCLUDING DISTRIBUTION.
• 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 percentage from 10 to 100. 100 (complete filling) is the default value. When a smaller fill factor 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 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. The parameter has no meaning for column–store tables.

    Value range: 10–100

  • ORIENTATION

    Determines the storage mode of the data in the table.

    Value range:

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

      orientation cannot be modified.

  • COMPRESSION
    • Valid values for column-store tables are LOW, MIDDLE, HIGH, YES, and NO, and the compression level increases accordingly. The default is LOW.
    • Row-store tables do not support compression.
  • MAX_BATCHROW

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

    Value range: 10000 to 60000

  • PARTIAL_CLUSTER_ROWS

    Specifies the number of records to be partially clustered for storage during data loading. The parameter is only valid for column-store tables.

    Value range: a number greater than or equal to 100000 The value is a multiple of MAX_BATCHROW.

  • DELTAROW_THRESHOLD

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

    Value range: 0 to 9999

  • 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 not advised 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 DNs.

  Value range:

  • REPLICATION: Each row in the table exists on all DNs, that is, each DN has complete table data.
  • HASH (column_name): Each row of the table will be placed into specified DNs based on the hash value of the specified column.
  

  • When DISTRIBUTE BY HASH (column_name) is specified, the primary key and its unique index must contain the column_name column.
  • When DISTRIBUTE BY HASH (column_name) in a referenced table is specified, the foreign key of the reference table must contain the column_name column.

  Default value: HASH(column_name), primary key column of column_name (if any) or column of the first data type that can be used as a distribution key.

  column_name supports the following data types:

  • INTEGER TYPES: TINYINT, SMALLINT, INT, BIGINT, NUMERIC/DECIMAL
  • CHARACTER TYPES: CHAR, BPCHAR, VARCHAR, VARCHAR2, NVARCHAR2
  • DATA/TIME TYPES: DATE, TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ, INTERVAL, SMALLDATETIME
• 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(partition_key)

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

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

  

  In this case, a maximum of four partition keys are supported.

  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.

  (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: SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, TIMESTAMP[(p)] [WITHOUT TIME ZONE], TIMESTAMP[(p)] [WITH TIME ZONE], and DATE.

• PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } )

  Specifies 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.
• 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 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 (32767).
  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.
• { ENABLE | DISABLE } ROW MOVEMENT

  Sets row movement.

  If the tuple value is updated on the partition key during the UPDATE action, 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 under 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, 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 when 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. The 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 second 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.

Examples

• Example 1: Create a range-partitioned table tpcds.web_returns_p1. The table has eight partitions and their partition keys are of the integer type. The ranges of the partitions are: wr_returned_date_sk < 2450815, 2450815 ≤ wr_returned_date_sk < 2451179, 2451179 ≤ wr_returned_date_sk < 2451544, 2451544 ≤ wr_returned_date_sk < 2451910, 2451910 ≤ wr_returned_date_sk < 2452275, 2452275 ≤ wr_returned_date_sk < 2452640, 2452640 ≤ wr_returned_date_sk < 2453005, and wr_returned_date_sk ≥ 2453005.

  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 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94

  -- Create the tpcds.web_returns table. openGauss=# CREATE TABLE tpcds.web_returns ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ); -- Create a range-partitioned table tpcds.web_returns_p1. openGauss=# CREATE TABLE tpcds.web_returns_p1 ( WR_RETURNED_DATE_SK INTEGER , WR_RETURNED_TIME_SK INTEGER , WR_ITEM_SK INTEGER NOT NULL, WR_REFUNDED_CUSTOMER_SK INTEGER , WR_REFUNDED_CDEMO_SK INTEGER , WR_REFUNDED_HDEMO_SK INTEGER , WR_REFUNDED_ADDR_SK INTEGER , WR_RETURNING_CUSTOMER_SK INTEGER , WR_RETURNING_CDEMO_SK INTEGER , WR_RETURNING_HDEMO_SK INTEGER , WR_RETURNING_ADDR_SK INTEGER , WR_WEB_PAGE_SK INTEGER , WR_REASON_SK INTEGER , WR_ORDER_NUMBER BIGINT NOT NULL, WR_RETURN_QUANTITY INTEGER , WR_RETURN_AMT DECIMAL(7,2) , WR_RETURN_TAX DECIMAL(7,2) , WR_RETURN_AMT_INC_TAX DECIMAL(7,2) , WR_FEE DECIMAL(7,2) , WR_RETURN_SHIP_COST DECIMAL(7,2) , WR_REFUNDED_CASH DECIMAL(7,2) , WR_REVERSED_CHARGE DECIMAL(7,2) , WR_ACCOUNT_CREDIT DECIMAL(7,2) , WR_NET_LOSS DECIMAL(7,2) ) WITH (ORIENTATION = COLUMN,COMPRESSION=MIDDLE) DISTRIBUTE BY HASH (WR_ITEM_SK) PARTITION BY RANGE(WR_RETURNED_DATE_SK) ( PARTITION P1 VALUES LESS THAN(2450815), PARTITION P2 VALUES LESS THAN(2451179), PARTITION P3 VALUES LESS THAN(2451544), PARTITION P4 VALUES LESS THAN(2451910), PARTITION P5 VALUES LESS THAN(2452275), PARTITION P6 VALUES LESS THAN(2452640), PARTITION P7 VALUES LESS THAN(2453005), PARTITION P8 VALUES LESS THAN(MAXVALUE) ); -- Import data from the example data table. openGauss=# INSERT INTO tpcds.web_returns_p1 SELECT * FROM tpcds.web_returns; -- Delete the P8 partition. openGauss=# ALTER TABLE tpcds.web_returns_p1 DROP PARTITION P8; -- Add a partition WR_RETURNED_DATE_SK with values ranging from 2453005 to 2453105. openGauss=# ALTER TABLE tpcds.web_returns_p1 ADD PARTITION P8 VALUES LESS THAN (2453105); -- Add a partition WR_RETURNED_DATE_SK with values ranging from 2453105 to MAXVALUE. openGauss=# ALTER TABLE tpcds.web_returns_p1 ADD PARTITION P9 VALUES LESS THAN (MAXVALUE); -- Delete the P8 partition. openGauss=# ALTER TABLE tpcds.web_returns_p1 DROP PARTITION FOR (2453005); -- Rename the P7 partition to P10. openGauss=# ALTER TABLE tpcds.web_returns_p1 RENAME PARTITION P7 TO P10; -- Rename the P6 partition to P11. openGauss=# ALTER TABLE tpcds.web_returns_p1 RENAME PARTITION FOR (2452639) TO P11; -- Query the number of rows in the P10 partition. openGauss=# SELECT count(*) FROM tpcds.web_returns_p1 PARTITION (P10); count -------- 0 (1 row) -- Query the number of rows in the P1 partition. openGauss=# SELECT COUNT(*) FROM tpcds.web_returns_p1 PARTITION FOR (2450815); count -------- 0 (1 row)

• Example 2: Create a range-partitioned table tpcds.web_returns_p2. The table has eight partitions and their partition keys are of the integer type. The upper limit of the eighth partition is MAXVALUE.

  The ranges of the partitions are: wr_returned_date_sk < 2450815, 2450815 ≤ wr_returned_date_sk < 2451179, 2451179 ≤ wr_returned_date_sk < 2451544, 2451544 ≤ wr_returned_date_sk < 2451910, 2451910 ≤ wr_returned_date_sk < 2452275, 2452275 ≤ wr_returned_date_sk < 2452640, 2452640 ≤ wr_returned_date_sk < 2453005, and wr_returned_date_sk ≥ 2453005.

  The tablespace of the tpcds.web_returns_p2 partitioned table is example1. Partitions P1 to P7 have no specified tablespaces, and use the example1 tablespace of the tpcds.web_returns_p2 partitioned table. The tablespace of the P8 partitioned table is example2.

  Assume that CN and DN data directory/pg_location/mount1/path1, CN and DN data directory/pg_location/mount2/path2, CN and DN data directory/pg_location/mount3/path3, and CN and DN data directory/pg_location/mount4/path4 are empty directories for which user dwsadmin has read and write permissions.

  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

  openGauss=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1'; openGauss=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2'; openGauss=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3'; openGauss=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4'; openGauss=# CREATE TABLE tpcds.web_returns_p2 ( WR_RETURNED_DATE_SK INTEGER , WR_RETURNED_TIME_SK INTEGER , WR_ITEM_SK INTEGER NOT NULL, WR_REFUNDED_CUSTOMER_SK INTEGER , WR_REFUNDED_CDEMO_SK INTEGER , WR_REFUNDED_HDEMO_SK INTEGER , WR_REFUNDED_ADDR_SK INTEGER , WR_RETURNING_CUSTOMER_SK INTEGER , WR_RETURNING_CDEMO_SK INTEGER , WR_RETURNING_HDEMO_SK INTEGER , WR_RETURNING_ADDR_SK INTEGER , WR_WEB_PAGE_SK INTEGER , WR_REASON_SK INTEGER , WR_ORDER_NUMBER BIGINT NOT NULL, WR_RETURN_QUANTITY INTEGER , WR_RETURN_AMT DECIMAL(7,2) , WR_RETURN_TAX DECIMAL(7,2) , WR_RETURN_AMT_INC_TAX DECIMAL(7,2) , WR_FEE DECIMAL(7,2) , WR_RETURN_SHIP_COST DECIMAL(7,2) , WR_REFUNDED_CASH DECIMAL(7,2) , WR_REVERSED_CHARGE DECIMAL(7,2) , WR_ACCOUNT_CREDIT DECIMAL(7,2) , WR_NET_LOSS DECIMAL(7,2) ) TABLESPACE example1 DISTRIBUTE BY HASH (WR_ITEM_SK) PARTITION BY RANGE(WR_RETURNED_DATE_SK) ( PARTITION P1 VALUES LESS THAN(2450815), PARTITION P2 VALUES LESS THAN(2451179), PARTITION P3 VALUES LESS THAN(2451544), PARTITION P4 VALUES LESS THAN(2451910), PARTITION P5 VALUES LESS THAN(2452275), PARTITION P6 VALUES LESS THAN(2452640), PARTITION P7 VALUES LESS THAN(2453005), PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2 ) ENABLE ROW MOVEMENT; -- Create a partitioned table using LIKE. openGauss=# CREATE TABLE tpcds.web_returns_p3 (LIKE tpcds.web_returns_p2 INCLUDING PARTITION); -- Change the tablespace of the P1 partition to example2. openGauss=# ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P1 TABLESPACE example2; -- Change the tablespace of the P2 partition to example3. openGauss=# ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P2 TABLESPACE example3; -- Split the P8 partition at 2453010. openGauss=# ALTER TABLE tpcds.web_returns_p2 SPLIT PARTITION P8 AT (2453010) INTO ( PARTITION P9, PARTITION P10 ); -- Merge the P6 and P7 partitions into one. openGauss=# ALTER TABLE tpcds.web_returns_p2 MERGE PARTITIONS P6, P7 INTO PARTITION P8; -- Modify the migration attribute of the partitioned table. openGauss=# ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT; -- Delete tables and tablespaces. openGauss=# DROP TABLE tpcds.web_returns_p1; openGauss=# DROP TABLE tpcds.web_returns_p2; openGauss=# DROP TABLE tpcds.web_returns_p3; openGauss=# DROP TABLESPACE example1; openGauss=# DROP TABLESPACE example2; openGauss=# DROP TABLESPACE example3; openGauss=# DROP TABLESPACE example4;

Helpful Links

ALTER TABLE PARTITION and DROP TABLE