• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. CREATE TABLE PARTITION

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 partition cable, and a physical piece is called a partition. Data is stored on these smaller physical pieces, namely, partitions, instead of the larger 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, the table is partitioned into ranges defined by a key column or set of 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.

Range partitioning policy: Data is mapped to a created partition based on the partition key value. If the data can be mapped to, it is inserted into the specific partition; if it cannot be mapped to, error messages are returned. This is the most commonly used partitioning policy.

Partitioning can provide several benefits:

  • Query performance can be improved dramatically 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.
  • When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of reads scattered across the whole table.
  • Bulk loads and deletion can be performed by adding or removing partitions, if that requirement is planned into the partitioning design. It also entirely avoids the VACUUM overhead caused by a bulk DELETE (only for range partitioning).

Precautions

A partitioned table supports unique and primary key constraints. The constraint keys of these constraints contain all partition keys.

Syntax

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 { 
        {VALUES (partition_key)} |
        {RANGE (partition_key) ( partition_less_than_item [, ... ] )} |
        {RANGE (partition_key) ( partition_start_end_item [, ... ] )}
    } [ { ENABLE | DISABLE } ROW MOVEMENT ]; 
  • column_constraint is as follows:
    [ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL | 
      CHECK ( expression ) | 
      DEFAULT default_expr | 
      UNIQUE 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 ]
  • like_option is as follows:
    { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | RELOPTIONS | DISTRIBUTION | ALL }
  • index_parameters is as follows:
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ USING INDEX TABLESPACE tablespace_name ]
  • partition_less_than_item is as follows:
    PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } ) [TABLESPACE tablespace_name]
  • partition_start_end_item is as follows:
    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})}
    } [TABLESPACE tablespace_name]

Parameter Description

  • IF NOT EXISTS

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

  • partition_table_name

    Name of the partitioned table

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

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

  • 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 a name for a column or table constraint. The optional constraint clauses specify constraints that new or updated rows must satisfy for an insert or update operation to succeed.

    There are two ways to define constraints:

    • A column constraint is defined as part of a column definition, and it is bound to a particular column.
    • A table constraint is not bound to any particular columns but can apply to more than one column.
  • 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 only be copied 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 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 source table will not be created on the new table, unless the INCLUDING INDEXES clause is specified.
    • STORAGE settings for the copied column definitions will only be copied if INCLUDING STORAGE is specified. The default behavior is to exclude STORAGE settings.
    • Comments for the copied columns, constraints, and indexes will only be copied if INCLUDING COMMENTS is specified. The default behavior is to exclude comments.
    • If INCLUDING RELOPTIONS is specified, the new table will copy the storage parameter (WITH clause of the source table) 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 column, and the new table cannot use DISTRIBUTE BY clause. The default behavior is to exclude distribution information of the source table.
    • INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS INCLUDING RELOPTIONS INCLUDING DISTRIBUTION.
  • WITH ( storage_parameter [= value] [, ... ] )

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

    • FILLFACTOR

      The fillfactor of a table is a percentage between 10 and 100. 100 (complete packing) is the default value. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage. The remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, setting the fillfactor to 100 (complete packing) is the best choice, but in heavily updated tables smaller fillfactors are 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.

      Valid value:

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

        orientation cannot be modified.

    • COMPRESSION
      • Value range: The valid value of column–store table is LOW/MIDDLE/HIGH/YES/NO, and the compression level increases accordingly.
      • The valid values for row-store tables are YES and NO, and the default is NO.
      • Valid values for an HDFS table are ZLIB, SNAPPY, and NO. ZLIB indicates a higher compression ratio than SNAPPY. The default is SNAPPY.
    • MAX_BATCHROW

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

      Value range: 10000 to 60000

    • PARTIAL_CLUSTER_ROWS

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

      Value range: The valid value is no less than 100000. The value is the multiple of MAX_BATCHROW.

    • DELTAROW_THRESHOLD

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

      Value range: 0 to 9999

  • COMPRESS / NOCOMPRESS

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

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

  • TABLESPACE tablespace_name

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

  • DISTRIBUTE BY

    Specifies how the table is distributed or replicated between DNs.

    Valid value:

    • 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 all the 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), the key column of column_name (if any) or the column of distribution column supported by first data type.

    column_name supports the following date 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 in which node group is the table created. Currently, it cannot be used for HDFS tables. 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 the information of partitions. partition_name is the name of a range partition. partition_value is the upper limit of range partition, and the value depends on the type of partition_key. MAXVALUE can specify the upper boundary of a range partition, and it is commonly used to specify the upper boundary of the last range partition.

    • Upper boundaries must be specified for each partition.
    • The types of upper boundaries must be the same as those of partition keys.
    • In a partition list, partitions are arranged in ascending order of upper boundary values. Therefore, a partition with a certain upper boundary value is placed before another partition with a larger upper boundary value.
  • } | {START (partition_value) END (partition_value|MAXVALUE)} | {START(partition_value)} | {END (partition_value | MAXVALUE)}

    Specifies partition definitions.

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

    Specifies the row movement switch.

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

    Valid value:

    • ENABLE: Row movement is enabled.
    • DISABLE (default value): Disable row movement.
  • NOT NULL

    Indicates that the column is not allowed to contain NULL values. ENABLE can be omitted.

  • NULL

    Indicates that the column is allowed to contain NULL values. This is the default setting.

    This clause is only provided for compatibility with non-standard SQL databases. You are advised not to use this clause.

  • CHECK (condition) [ NO INHERIT ]

    Specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. If any row of an insert or update operation produces a FALSE result, an error exception is raised and the insert or update does not alter the database.

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

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

    ENABLE can be omitted.

  • DEFAULT default_expr

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

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

  • UNIQUE index_parameters

    UNIQUE ( column_name [, ... ] ) 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.

    NOTE:

    If DISTRIBUTE BY REPLICATION is not specified, the column table that contains only unique values must contain distribution columns.

  • PRIMARY KEY index_parameters

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

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

    Only one primary key can be specified for a table.

    NOTE:

    If DISTRIBUTE BY REPLICATION is not specified, the column set with a primary key constraint must contain distributed columns.

  • DEFERRABLE | NOT DEFERRABLE

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

  • INITIALLY IMMEDIATE | INITIALLY DEFERRED

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

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

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

  • USING INDEX TABLESPACE tablespace_name

    Allows selection of the tablespace in which the index associated with a UNIQUE or PRIMARY KEY constraint will be created. If not specified, default_tablespace is consulted, or the default tablespace in the database if temp_tablespaces is empty.

Examples

  • Example 1: Create a range-partitioned table tpcds.web_returns_p1. The table has eight partitions and their partition keys are of type integer. 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.
    -- Create a range-partitioned table tpcds.web_returns_p1:
    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:
    INSERT INTO tpcds.web_returns_p1 SELECT * FROM tpcds.web_returns;
    
    -- Delete the P8 partition:
    ALTER TABLE tpcds.web_returns_p1 DROP PARTITION P8;
    
    -- Add a partition WR_RETURNED_DATE_SK with values ranging from 2453005 to 2453105:
    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 and MAXVALUE:
    ALTER TABLE tpcds.web_returns_p1 ADD PARTITION P9 VALUES LESS THAN (MAXVALUE);
    
    -- Delete the P8 partition:
    ALTER TABLE tpcds.web_returns_p1 DROP PARTITION FOR (2453005);
    
    -- Rename the P7 partition as P10:
    ALTER TABLE tpcds.web_returns_p1 RENAME PARTITION P7 TO P10;
    
    -- Rename the P6 partition as P11:
    ALTER TABLE tpcds.web_returns_p1 RENAME PARTITION FOR (2452639) TO P11;
    
    -- Query rows in the P10 partition:
    SELECT count(*) FROM tpcds.web_returns_p1 PARTITION (P10);
     count  
    --------
     929340
    (1 row)
    
    -- Query the number of rows in the P1 partition:
    SELECT COUNT(*) FROM tpcds.web_returns_p1 PARTITION FOR (2450815);
     count  
    --------
     446854
    (1 row)
  • Example 2: Create a range partitioned table tpcds.web_returns_p2. The table has eight partitions and their partition keys are of type integer. The upper limit of the eighth partition is MAXVALUE.

    The ranges of the eight 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 /home/dbadmin/tbspc1, /home/dbadmin/tbspc2, /home/dbadmin/tbspc3, and /home/dbadmin/tbspc4 are empty directories for which user dwsadmin has the read/write permission.

    CREATE TABLESPACE example1 LOCATION '/home/dbadmin/tbspc1';
    CREATE TABLESPACE example2 LOCATION '/home/dbadmin/tbspc2';
    CREATE TABLESPACE example3 LOCATION '/home/dbadmin/tbspc3';
    CREATE TABLESPACE example4 LOCATION '/home/dbadmin/tbspc4';
    
    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:
    CREATE TABLE tpcds.web_returns_p3 (LIKE tpcds.web_returns_p2 INCLUDING PARTITION);
    
    -- Change the tablespace of the P1 partition to example2:
    ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P1 TABLESPACE example2;
    
    -- Change the tablespace of the P2 partition to example3:
    ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P2 TABLESPACE example3;
    
    -- Split the P8 partition at 2453010:
    ALTER TABLE tpcds.web_returns_p2 SPLIT PARTITION P8 AT (2453010) INTO
    (
            PARTITION P9,
            PARTITION P10
    ); 
    
    -- Merge the P6 and P7 partitions into one:
    ALTER TABLE tpcds.web_returns_p2 MERGE PARTITIONS P6, P7 INTO PARTITION P8;
    
    -- Modify the migration attribute of a partitioned table:
    ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;
    -- Delete tables and tablespaces.
    DROP TABLE tpcds.web_returns_p1;
    DROP TABLE tpcds.web_returns_p2;
    DROP TABLE tpcds.web_returns_p3;
    DROP TABLESPACE example1;
    DROP TABLESPACE example2;
    DROP TABLESPACE example3;
    DROP TABLESPACE example4;
  • Example 3: Use START END to create and modify a range partitioned table.

    Assume that /home/dbadmin/startend_tbs1, /home/dbadmin/startend_tbs2, /home/dbadmin/startend_tbs3, and /home/dbadmin/startend_tbs4 are empty directories for which user dbadmin has the read/write permission.

    -- Create tablespaces.
    CREATE TABLESPACE startend_tbs1 LOCATION '/home/dbadmin/startend_tbs1';
    CREATE TABLESPACE startend_tbs2 LOCATION '/home/dbadmin/startend_tbs2';
    CREATE TABLESPACE startend_tbs3 LOCATION '/home/dbadmin/startend_tbs3';
    CREATE TABLESPACE startend_tbs4 LOCATION '/home/dbadmin/startend_tbs4';
    
    -- Create a temporary schema.
    CREATE SCHEMA tpcds;
    SET CURRENT_SCHEMA TO tpcds;
    
    -- Create a partitioned table with the partition key of type integer.
    CREATE TABLE tpcds.startend_pt (c1 INT, c2 INT) 
    TABLESPACE startend_tbs1 
    DISTRIBUTE BY HASH (c1) 
    PARTITION BY RANGE (c2) (
        PARTITION p1 START(1) END(1000) EVERY(200) TABLESPACE startend_tbs2,
        PARTITION p2 END(2000),
        PARTITION p3 START(2000) END(2500) TABLESPACE startend_tbs3,
        PARTITION p4 START(2500),
        PARTITION p5 START(3000) END(5000) EVERY(1000) TABLESPACE startend_tbs4
    )
    ENABLE ROW MOVEMENT;
    
    -- View the information of the partitioned table.
    SELECT relname, boundaries, spcname FROM pg_partition p JOIN pg_tablespace t ON p.reltablespace=t.oid and p.parentid='tpcds.startend_pt'::regclass ORDER BY 1;
       relname   | boundaries |    spcname
    -------------+------------+---------------
     p1_0        | {1}        | startend_tbs2
     p1_1        | {201}      | startend_tbs2
     p1_2        | {401}      | startend_tbs2
     p1_3        | {601}      | startend_tbs2
     p1_4        | {801}      | startend_tbs2
     p1_5        | {1000}     | startend_tbs2
     p2          | {2000}     | startend_tbs1
     p3          | {2500}     | startend_tbs3
     p4          | {3000}     | startend_tbs1
     p5_1        | {4000}     | startend_tbs4
     p5_2        | {5000}     | startend_tbs4
     startend_pt |            | startend_tbs1
    (12 rows)
    
    -- Import data and check the data volume in the partition.
    INSERT INTO tpcds.startend_pt VALUES (GENERATE_SERIES(0, 4999), GENERATE_SERIES(0, 4999));
    SELECT COUNT(*) FROM tpcds.startend_pt PARTITION FOR (0);
     count
    -------
         1
    (1 row)
    
    SELECT COUNT(*) FROM tpcds.startend_pt PARTITION (p3);
     count
    -------
       500
    (1 row)
    
    -- Add partitions [5000, 5300), [5300, 5600), [5600, 5900), and [5900, 6000).
    ALTER TABLE tpcds.startend_pt ADD PARTITION p6 START(5000) END(6000) EVERY(300) TABLESPACE startend_tbs4;
    
    -- Add the partition p7, specified by MAXVALUE.
    ALTER TABLE tpcds.startend_pt ADD PARTITION p7 END(MAXVALUE);
    
    -- Rename the partition p7 to p8.
    ALTER TABLE tpcds.startend_pt RENAME PARTITION p7 TO p8;
    
    -- Delete the partition p8.
    ALTER TABLE tpcds.startend_pt DROP PARTITION p8;
    
    -- Rename the partition where 5950 is located to p71.
    ALTER TABLE tpcds.startend_pt RENAME PARTITION FOR(5950) TO p71;
    
    -- Split the partition [4000, 5000) where 4500 is located.
    ALTER TABLE tpcds.startend_pt SPLIT PARTITION FOR(4500) INTO(PARTITION q1 START(4000) END(5000) EVERY(250) TABLESPACE startend_tbs3);
    
    -- Change the tablespace of the partition p2 to startend_tbs4.
    ALTER TABLE tpcds.startend_pt MOVE PARTITION p2 TABLESPACE startend_tbs4;
    
    -- View the partition status.
    SELECT relname, boundaries, spcname FROM pg_partition p JOIN pg_tablespace t ON p.reltablespace=t.oid and p.parentid='tpcds.startend_pt'::regclass ORDER BY 1;
       relname   | boundaries |    spcname
    -------------+------------+---------------
     p1_0        | {1}        | startend_tbs2
     p1_1        | {201}      | startend_tbs2
     p1_2        | {401}      | startend_tbs2
     p1_3        | {601}      | startend_tbs2
     p1_4        | {801}      | startend_tbs2
     p1_5        | {1000}     | startend_tbs2
     p2          | {2000}     | startend_tbs4
     p3          | {2500}     | startend_tbs3
     p4          | {3000}     | startend_tbs1
     p5_1        | {4000}     | startend_tbs4
     p6_1        | {5300}     | startend_tbs4
     p6_2        | {5600}     | startend_tbs4
     p6_3        | {5900}     | startend_tbs4
     p71         | {6000}     | startend_tbs4
     q1_1        | {4250}     | startend_tbs3
     q1_2        | {4500}     | startend_tbs3
     q1_3        | {4750}     | startend_tbs3
     q1_4        | {5000}     | startend_tbs3
     startend_pt |            | startend_tbs1
    (19 rows)
    
    -- Delete tables and tablespaces.
    DROP SCHEMA tpcds CASCADE;
    DROP TABLESPACE startend_tbs1;
    DROP TABLESPACE startend_tbs2;
    DROP TABLESPACE startend_tbs3;
    DROP TABLESPACE startend_tbs4;

Helpful Links

, DROP TABLE