Thank you for rating our AI Search!

We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.

Your feedback will help us improve AI Search experience.

Hello, I’m Arenadata AI!

Select language

EN | RU

Change color mode

Home
Arenadata DB
Data management
Overview of database objects
Tables
Partitioning

Partitioning

Daria Barysheva

Collapse content Expand content

Contents

Overview
Create a partitioned table
Partition an existing table
View partitioned tables
Operations with partitions

Overview

Partitioning is a way to increase query performance by logically dividing large tables (such as fact tables) into smaller, more manageable parts called partitions. Due to partitioning, query optimizers can read a limited number of table rows instead of scanning all the table contents each time.

The exact partition that is selected to assign a new table record depends on the partition key — a column (or a set of columns for multi-level partitioning) that you define when creating a partitioned table.

Partitioning is performed only at the logical level. Unlike table distribution, partitioning does not divide tables physically. Both partitioned and non-partitioned tables are distributed across cluster segments.

Partitioning and distribution

Despite the advantages of partitioning, not all tables are good candidates for its application. Also, the more partitions you create — the slower cluster management can become in the future. The following are the conditions under which partitioning can improve the performance of data queries. Use partitioning if all or most of these points can be applied to your database:

There is a large table of facts. Large fact tables with millions of rows are good candidates for partitioning. On the other hand, smaller tables with thousands of records or fewer are unlikely to impact query performance significantly.
You are not satisfied with the current performance of data queries. Apply partitioning to the table only if queries against this table are far slower than required. This is a general condition for all performance tuning initiatives.
There is a field in which the table can be divided into approximately equal parts. Try to select the partition key in such a way that the numbers of rows in partitions are close. The more evenly the division into small chunks is made, the greater the benefits of partitioning you can get. For example, by dividing a table into 10 identical partitions, you can increase the query speed to 10 times than that of the original non-partitioned table (provided that the partition key is used in predicates — see the next item below).
Most queries that you want to speed up use the partition key in predicates. Partitioning improves query performance only if the query optimizer can select partitions based on the query predicates. Data queries that scan all partitions work even slower than if the table was not partitioned at all. Make sure that your query plans contain partition elimination.
There are business requirements for maintaining historical data. Partitioning is the best choice if you need to store data for a limited time period. For example, if you require data for the last 12 months to be stored in the database, it is very easy to drop the oldest partition and load actual data into the new one.

Partitioning limitations

There are some limitations that you should consider before using table partitioning:

A partitioned table can have a maximum of 32767 partitions for each partition level.
A primary key or a unique constraint of a partitioned table should contain the partition key.
Tables having the DISTRIBUTED REPLICATED distribution policy cannot be partitioned.
Loading data into the partitioned tables is extremely inefficient. Therefore, it is recommended to insert new data into staging tables and then apply to the partitioned table the EXCHANGE PARTITION command.
The query optimizer can selectively scan partitioned tables only when the query predicate contains:
- Immutable operators such as: =, <, <=, >, >=, and <>.
- STABLE and IMMUTABLE functions (not VOLATILE).
There are additional limitations for partitioned tables when a leaf partition is an external table.

Create a partitioned table

Syntax

ADB 7 retains most aspects of the partitioning syntax of ADB 6, referred to as the classic syntax. Additionally, ADB 7 introduces support for a modern syntax, derived from the PostgreSQL declarative partitioning syntax. The classic syntax is provided for backwards compatibility with previous ADB versions. The classic and modern partitioning syntaxes are alternatives, you can choose the most suitable of them for your needs. But it is not recommended to mix both syntaxes for partition maintenance operations. All subsequent examples are shown for both syntax forms.

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

You can create a partitioned table using the CREATE TABLE command with the PARTITION BY (and optionally the SUBPARTITION BY) clause. The command syntax depends on the partitioning type: range, list, or combination of types (multi-level partitioning):

Range partitioning. Range partitioning performs data division based on a numerical or date/timestamp range. You should use the PARTITION BY RANGE clause, then define the column to be used as a partition key, and then describe the intervals for partitions via the START and the END keywords. The INCLUSIVE and EXCLUSIVE keywords are used in conjunction with START and END to determine whether boundary values should be included in the range. By default, START values are inclusive and END values are exclusive. There are two ways to define range partitions — automatically and manually:
- Generate partitions automatically. To generate partitions automatically, use the START and the END clauses to specify the boundaries of the entire range. Then write the EVERY clause to define the partition increment value. For date ranges, this clause requires the interval unit: day, month, or year (e.g. EVERY(INTERVAL '1 month')). For numeric ranges, enter the number only (e.g. EVERY(1)). The DBMS automatically creates the required number of partitions, splitting the entire range of values of the specified column with a given step.
- Define partitions manually. An alternative way of specifying a partitioned table is to describe each partition individually. In this case you should add a comma-separated list of partition specifications in the parentheses after the PARTITION BY RANGE clause. Every specification should start with the PARTITION <partition_name> clause, where <partition_name> — a text to be used instead of the automatically generated number in the full partition name. The rest part of the specification contains the standard START and END clauses, which define individual intervals for each partition. Notice that there is no need to declare the END value for each partition, only for the last one.
List partitioning. List partitioning performs data division based on a predefined list of partition key values. Use the PARTITION BY LIST clause, then define the column to be used as a partition key, and then add a comma-separated list of partition specifications in the parentheses. Each specification should use the following template: PARTITION <partition_name> VALUES (<list_value> [,…]), where <partition_name> — a text to be used instead of the automatically generated number in the full partition name; <list_value> — the partition key values required for the current partition.
Multi-level partitioning. Multi-level partitioning is a combination of range and/or list types on multiple levels. It allows you to divide partitions at the first level into subpartitions at the second level, and each of them in turn — into subpartitions at the third level and so on. The PARTITION BY (RANGE or LIST) clause is used to define the partition key for the first partitioning level. The SUBPARTITION BY (RANGE or LIST) clause is used for all other levels. All subpartition specifications are similar to partition specifications, except that the SUBPARTITION keyword is used instead of PARTITION. There are two ways to define subpartitions — with a subpartition template or without:
- Use a subpartition template. In this case you specify subpartitions for each hierarchy level only once via the SUBPARTITION TEMPLATE clause. Each template is defined just after the corresponding SUBPARTITION BY clause. Using a subpartition template ensures that every partition has the same design, including the partitions that you add later.
- Define subpartitions manually. In this case you describe every partition and subpartition individually. At first, set all partition keys via the PARTITION BY and SUBPARTITION BY clauses. Then add partition and subpartition specifications. The hierarchy is defined by parentheses.

For all partitioning types, you can define the default partition using the DEFAULT PARTITION (or DEFAULT SUBPARTITION) clause after each partition (or subpartition) specification. The default partition is designed to add data that does not fit into a given range (or list) of values.

You can create a root partitioned table using the CREATE TABLE command with the PARTITION BY clause, then define the column to be used as a partition key. After that, you need to describe each partition separately using the CREATE TABLE … PARTITION OF … FOR VALUES … [PARTITION BY …] command. The command syntax depends on the partitioning type: range, list, hash, or combination of types (multi-level partitioning):

Range partitioning. Range partitioning performs data division based on a numerical or date/timestamp range. When creating a root partitioned table, use the PARTITION BY RANGE clause, then define the column to be used as a partition key. When creating each partition, describe the desired range of values using the FOR VALUES FROM (<start_value>) TO (<end_value>) clause.
List partitioning. List partitioning performs data division based on a predefined list of partition key values. When creating a root partitioned table, use the PARTITION BY LIST clause, then define the column to be used as a partition key. When creating each partition, describe the desired list of values using the FOR VALUES IN (<list_values>) clause.
Hash partitioning. Hash partitioning is available starting with ADB 7 with the modern partitioning syntax only. Each partition contains the rows for which the hash value of the partition key divided by the specified modulus produces the specified remainder. When creating a root partitioned table, use the PARTITION BY HASH clause, then define the column to be used as a partition key. When creating each partition, specify a modulus and a reminder using the FOR VALUES WITH (MODULUS <modulus_value>, REMAINDER <remainder_value>) clause.
Multi-level partitioning. Multi-level partitioning is a combination of various partitioning types on multiple levels. It allows you to divide partitions at the first level into subpartitions at the second level, and each of them in turn — into subpartitions at the third level and so on. The PARTITION BY (RANGE, LIST, or HASH) clause is used to define the partition key for each partitioning level.

For all partitioning types, you can define the default partition using the DEFAULT clause after each partition specification. The default partition is designed to add data that does not fit into a given range (or list) of values.

IMPORTANT

If data beyond the specified range/list is possible in the table — it is recommended to specify default partitions. Otherwise, there will be errors when inserting data. But remember that default partitions are always scanned by query optimizers. Therefore, default partitions that contain data can slow down the overall scan time.
Be careful when using multi-level partitioning. A large number of subpartitions can significantly increase the query processing time. Try to reduce the number of subpartitions created, especially with no or little data.
Automatic generation of partitions based on ranges (mentioned above for classic syntax) applies only to creation of an original partitioned table (within the selected range). In absence of a suitable partition among existing ones, new data will be added to the default partition (if available): no new partitions will be generated automatically later.

Examples

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

Date range partitioning (automatically)

The following example creates twelve partitions based on the date range — one partition for each month of the 2022 year. The default partition is also created.

CREATE TABLE book_order
  (id INT,
   book_id INT,
   client_id INT,
   book_count SMALLINT,
   order_date DATE
  )
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(order_date)
(START(date '2022-01-01') INCLUSIVE
 END(date '2023-01-01') EXCLUSIVE
 EVERY(INTERVAL '1 month'),
 DEFAULT PARTITION other);

Numeric range partitioning (automatically)

The following example creates ten partitions based on the numeric range — one partition for each year (from 2013 to 2022). It also adds the default partition. Compared to using date ranges, you should not specify explicitly the data type in the START, END clauses and define the interval unit in the EVERY clause. The rest syntax is the same.

CREATE TABLE book
  (id INT,
   title TEXT,
   author_id INT NOT NULL,
   public_year SMALLINT NULL,
   type_id INT NOT NULL,
   cover_id INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(public_year)
(START(2013)
 END(2023)
 EVERY(1),
 DEFAULT PARTITION other);

Date range partitioning (manually)

The following example creates the table book_order_manual with the structure completely identical to the book_order table created above, except that each partition is defined individually.

CREATE TABLE book_order_manual
  (id INT,
   book_id INT,
   client_id INT,
   book_count SMALLINT,
   order_date DATE
  )
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(order_date)
(PARTITION Jan22 START(date '2022-01-01') INCLUSIVE,
 PARTITION Feb22 START(date '2022-02-01') INCLUSIVE,
 PARTITION Mar22 START(date '2022-03-01') INCLUSIVE,
 PARTITION Apr22 START(date '2022-04-01') INCLUSIVE,
 PARTITION May22 START(date '2022-05-01') INCLUSIVE,
 PARTITION Jun22 START(date '2022-06-01') INCLUSIVE,
 PARTITION Jul22 START(date '2022-07-01') INCLUSIVE,
 PARTITION Aug22 START(date '2022-08-01') INCLUSIVE,
 PARTITION Sep22 START(date '2022-09-01') INCLUSIVE,
 PARTITION Oct22 START(date '2022-10-01') INCLUSIVE,
 PARTITION Nov22 START(date '2022-11-01') INCLUSIVE,
 PARTITION Dec22 START(date '2022-12-01') INCLUSIVE END(date '2023-01-01') EXCLUSIVE,
 DEFAULT PARTITION other);

Numeric range partitioning (manually)

The following example creates the table book_manual with the structure completely identical to the book table created above, except that each partition is defined individually.

CREATE TABLE book_manual
  (id INT,
   title TEXT,
   author_id INT NOT NULL,
   public_year SMALLINT NULL,
   type_id INT NOT NULL,
   cover_id INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(public_year)
(PARTITION Year2013 START(2013),
 PARTITION Year2014 START(2014),
 PARTITION Year2015 START(2015),
 PARTITION Year2016 START(2016),
 PARTITION Year2017 START(2017),
 PARTITION Year2018 START(2018),
 PARTITION Year2019 START(2019),
 PARTITION Year2020 START(2020),
 PARTITION Year2021 START(2021),
 PARTITION Year2022 START(2022) END(2023),
 DEFAULT PARTITION other);

List partitioning

The following example defines two partitions based on the gender column value. The default partition is also created.

CREATE TABLE client (id INT, name TEXT, gender CHAR(1))
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
(PARTITION girls VALUES ('F'),
 PARTITION boys VALUES ('M'),
 DEFAULT PARTITION other);

Multi-level partitioning (with a subpartition template)

The following example shows three-level partitioning. The command defines three range partitions at the first level and then divides each of them into three list subpartitions at the second level — based on the region column value. These subpartitions, in turn, are divided into two list subpartitions at the third level — based on the fiction column value. At each level of the hierarchy, the default partition/subpartition is also added.

CREATE TABLE sales
 (id INT,
  date DATE,
  fiction CHAR(1),
  region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
   SUBPARTITION BY LIST (region)
     SUBPARTITION TEMPLATE (
       SUBPARTITION rus VALUES ('rus'),
       SUBPARTITION asia VALUES ('asia'),
       SUBPARTITION europe VALUES ('europe'),
       DEFAULT SUBPARTITION other_rg
     )

     SUBPARTITION BY LIST (fiction)
       SUBPARTITION TEMPLATE (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )

(START(date '2022-01-01') INCLUSIVE
 END(date '2022-04-01') EXCLUSIVE
 EVERY(INTERVAL '1 month'),
 DEFAULT PARTITION other_dt
);

Multi-level partitioning (manually)

The following example creates the table sales_manual with the structure completely identical to the sales table created above, except that all subpartitions are defined individually at every level. You can see that the command text is much longer, but the result is the same.

CREATE TABLE sales_manual
 (id INT,
  date DATE,
  fiction CHAR(1),
  region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
  SUBPARTITION BY LIST (region)
    SUBPARTITION BY LIST (fiction)

(PARTITION Jan2022 START(date '2022-01-01') INCLUSIVE
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  ),
 PARTITION Feb2022 START(date '2022-02-01') INCLUSIVE
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  ),
 PARTITION Mar2022 START(date '2022-03-01') INCLUSIVE
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  ),
 DEFAULT PARTITION other_dt
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  )
);

Date range partitioning

The following example creates twelve partitions based on the date range — one partition for each month of the 2022 year. The default partition is also created.

CREATE TABLE book_order_manual
  (id INT,
   book_id INT,
   client_id INT,
   book_count SMALLINT,
   order_date DATE
  )
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(order_date);
CREATE TABLE book_order_manual_1_prt_jan22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE book_order_manual_1_prt_feb22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE book_order_manual_1_prt_mar22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
CREATE TABLE book_order_manual_1_prt_apr22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE TABLE book_order_manual_1_prt_may22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-05-01') TO ('2022-06-01');
CREATE TABLE book_order_manual_1_prt_jun22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-06-01') TO ('2022-07-01');
CREATE TABLE book_order_manual_1_prt_jul22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-07-01') TO ('2022-08-01');
CREATE TABLE book_order_manual_1_prt_aug22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-08-01') TO ('2022-09-01');
CREATE TABLE book_order_manual_1_prt_sep22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-09-01') TO ('2022-10-01');
CREATE TABLE book_order_manual_1_prt_oct22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-10-01') TO ('2022-11-01');
CREATE TABLE book_order_manual_1_prt_nov22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-11-01') TO ('2022-12-01');
CREATE TABLE book_order_manual_1_prt_dec22 PARTITION OF book_order_manual FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');
CREATE TABLE book_order_manual_1_prt_other PARTITION OF book_order_manual DEFAULT;

Numeric range partitioning

The following example creates ten partitions based on the numeric range — one partition for each year (from 2013 to 2022). It also adds the default partition.

CREATE TABLE book_manual
  (id INT,
   title TEXT,
   author_id INT NOT NULL,
   public_year SMALLINT NULL,
   type_id INT NOT NULL,
   cover_id INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(public_year);
CREATE TABLE book_manual_1_prt_year2013 PARTITION OF book_manual FOR VALUES FROM (2013) TO (2014);
CREATE TABLE book_manual_1_prt_year2014 PARTITION OF book_manual FOR VALUES FROM (2014) TO (2015);
CREATE TABLE book_manual_1_prt_year2015 PARTITION OF book_manual FOR VALUES FROM (2015) TO (2016);
CREATE TABLE book_manual_1_prt_year2016 PARTITION OF book_manual FOR VALUES FROM (2016) TO (2017);
CREATE TABLE book_manual_1_prt_year2017 PARTITION OF book_manual FOR VALUES FROM (2017) TO (2018);
CREATE TABLE book_manual_1_prt_year2018 PARTITION OF book_manual FOR VALUES FROM (2018) TO (2019);
CREATE TABLE book_manual_1_prt_year2019 PARTITION OF book_manual FOR VALUES FROM (2019) TO (2020);
CREATE TABLE book_manual_1_prt_year2020 PARTITION OF book_manual FOR VALUES FROM (2020) TO (2021);
CREATE TABLE book_manual_1_prt_year2021 PARTITION OF book_manual FOR VALUES FROM (2021) TO (2022);
CREATE TABLE book_manual_1_prt_year2022 PARTITION OF book_manual FOR VALUES FROM (2022) TO (2023);
CREATE TABLE book_manual_1_prt_other PARTITION OF book_manual DEFAULT;

List partitioning

The following example defines two partitions based on the gender column value. The default partition is also created.

CREATE TABLE client (id INT, name TEXT, gender CHAR(1))
DISTRIBUTED BY (id)
PARTITION BY LIST (gender);
CREATE TABLE client_1_prt_girls PARTITION OF client FOR VALUES IN('F');
CREATE TABLE client_1_prt_boys PARTITION OF client FOR VALUES IN('M');
CREATE TABLE client_1_prt_other PARTITION OF client DEFAULT;

Multi-level partitioning

The following example shows three-level partitioning. The commands define three range partitions at the first level and then divide each of them into three list subpartitions at the second level — based on the region column value. These subpartitions, in turn, are divided into two list subpartitions at the third level — based on the fiction column value. At each level of the hierarchy, the default partition/subpartition is also added.

CREATE TABLE sales_manual
 (id INT,
  date DATE,
  fiction CHAR(1),
  region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date);

CREATE TABLE sales_manual_1_prt_other_dt PARTITION OF sales_manual DEFAULT PARTITION BY LIST (region);
CREATE TABLE sales_manual_1_prt_jan2022 PARTITION OF sales_manual FOR VALUES FROM ('2022-01-01') TO ('2022-02-01') PARTITION BY LIST (region);
CREATE TABLE sales_manual_1_prt_feb2022 PARTITION OF sales_manual FOR VALUES FROM ('2022-02-01') TO ('2022-03-01') PARTITION BY LIST (region);
CREATE TABLE sales_manual_1_prt_mar2022 PARTITION OF sales_manual FOR VALUES FROM ('2022-03-01') TO ('2022-04-01') PARTITION BY LIST (region);

CREATE TABLE sales_manual_1_prt_other_dt_2_prt_other_rg PARTITION OF sales_manual_1_prt_other_dt DEFAULT PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_rus  PARTITION OF sales_manual_1_prt_other_dt FOR VALUES IN('rus') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_asia  PARTITION OF sales_manual_1_prt_other_dt FOR VALUES IN('asia') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_europe  PARTITION OF sales_manual_1_prt_other_dt FOR VALUES IN('europe') PARTITION BY LIST (fiction);

CREATE TABLE sales_manual_1_prt_jan2022_2_prt_other_rg PARTITION OF sales_manual_1_prt_jan2022 DEFAULT PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_rus PARTITION OF sales_manual_1_prt_jan2022 FOR VALUES IN('rus') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_asia PARTITION OF sales_manual_1_prt_jan2022 FOR VALUES IN('asia') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_europe PARTITION OF sales_manual_1_prt_jan2022 FOR VALUES IN('europe') PARTITION BY LIST (fiction);

CREATE TABLE sales_manual_1_prt_feb2022_2_prt_other_rg PARTITION OF sales_manual_1_prt_feb2022 DEFAULT PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_rus PARTITION OF sales_manual_1_prt_feb2022 FOR VALUES IN('rus') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_asia PARTITION OF sales_manual_1_prt_feb2022 FOR VALUES IN('asia') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_europe PARTITION OF sales_manual_1_prt_feb2022 FOR VALUES IN('europe') PARTITION BY LIST (fiction);

CREATE TABLE sales_manual_1_prt_mar2022_2_prt_other_rg PARTITION OF sales_manual_1_prt_mar2022 DEFAULT PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_rus PARTITION OF sales_manual_1_prt_mar2022 FOR VALUES IN('rus') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_asia PARTITION OF sales_manual_1_prt_mar2022 FOR VALUES IN('asia') PARTITION BY LIST (fiction);
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_europe PARTITION OF sales_manual_1_prt_mar2022 FOR VALUES IN('europe') PARTITION BY LIST (fiction);

CREATE TABLE sales_manual_1_prt_other_dt_2_prt_other_rg_3_prt_other_tp PARTITION OF sales_manual_1_prt_other_dt_2_prt_other_rg DEFAULT;
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_other_rg_3_prt_fiction PARTITION OF sales_manual_1_prt_other_dt_2_prt_other_rg FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_other_rg_3_prt_non_fiction PARTITION OF sales_manual_1_prt_other_dt_2_prt_other_rg FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_other_dt_2_prt_rus_3_prt_other_tp PARTITION OF sales_manual_1_prt_other_dt_2_prt_rus DEFAULT;
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_rus_3_prt_fiction PARTITION OF sales_manual_1_prt_other_dt_2_prt_rus FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_rus_3_prt_non_fiction  PARTITION OF sales_manual_1_prt_other_dt_2_prt_rus FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_other_dt_2_prt_asia_3_prt_other_tp PARTITION OF sales_manual_1_prt_other_dt_2_prt_asia DEFAULT;
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_asia_3_prt_fiction PARTITION OF sales_manual_1_prt_other_dt_2_prt_asia FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_asia_3_prt_non_fiction PARTITION OF sales_manual_1_prt_other_dt_2_prt_asia FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_other_dt_2_prt_europe_3_prt_other_tp PARTITION OF sales_manual_1_prt_other_dt_2_prt_europe DEFAULT;
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_europe_3_prt_fiction PARTITION OF sales_manual_1_prt_other_dt_2_prt_europe FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_other_dt_2_prt_europe_3_prt_non_fiction PARTITION OF sales_manual_1_prt_other_dt_2_prt_europe FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_jan2022_2_prt_other_rg_3_prt_other_tp PARTITION OF sales_manual_1_prt_jan2022_2_prt_other_rg DEFAULT;
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_other_rg_3_prt_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_other_rg FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_other_rg_3_prt_non_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_other_rg FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_jan2022_2_prt_rus_3_prt_other_tp PARTITION OF sales_manual_1_prt_jan2022_2_prt_rus DEFAULT;
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_rus_3_prt_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_rus FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_rus_3_prt_non_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_rus FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_jan2022_2_prt_asia_3_prt_other_tp PARTITION OF sales_manual_1_prt_jan2022_2_prt_asia DEFAULT;
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_asia FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_asia_3_prt_non_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_asia FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_jan2022_2_prt_europe_3_prt_other_tp PARTITION OF sales_manual_1_prt_jan2022_2_prt_europe DEFAULT;
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_europe_3_prt_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_europe FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_jan2022_2_prt_europe_3_prt_non_fiction PARTITION OF sales_manual_1_prt_jan2022_2_prt_europe FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_feb2022_2_prt_other_rg_3_prt_other_tp PARTITION OF sales_manual_1_prt_feb2022_2_prt_other_rg DEFAULT;
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_other_rg_3_prt_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_other_rg FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_other_rg_3_prt_non_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_other_rg FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_feb2022_2_prt_rus_3_prt_other_tp PARTITION OF sales_manual_1_prt_feb2022_2_prt_rus DEFAULT;
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_rus_3_prt_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_rus FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_rus_3_prt_non_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_rus FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_feb2022_2_prt_asia_3_prt_other_tp PARTITION OF sales_manual_1_prt_feb2022_2_prt_asia DEFAULT;
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_asia_3_prt_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_asia FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_asia_3_prt_non_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_asia FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_feb2022_2_prt_europe_3_prt_other_tp PARTITION OF sales_manual_1_prt_feb2022_2_prt_europe DEFAULT;
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_europe_3_prt_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_europe FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_feb2022_2_prt_europe_3_prt_non_fiction PARTITION OF sales_manual_1_prt_feb2022_2_prt_europe FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_mar2022_2_prt_other_rg_3_prt_other_tp PARTITION OF sales_manual_1_prt_mar2022_2_prt_other_rg DEFAULT;
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_other_rg_3_prt_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_other_rg FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_other_rg_3_prt_non_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_other_rg FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_mar2022_2_prt_rus_3_prt_other_tp PARTITION OF sales_manual_1_prt_mar2022_2_prt_rus DEFAULT;
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_rus_3_prt_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_rus FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_rus_3_prt_non_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_rus FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_mar2022_2_prt_asia_3_prt_other_tp PARTITION OF sales_manual_1_prt_mar2022_2_prt_asia DEFAULT;
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_asia_3_prt_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_asia FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_asia_3_prt_non_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_asia FOR VALUES IN('n');

CREATE TABLE sales_manual_1_prt_mar2022_2_prt_europe_3_prt_other_tp PARTITION OF sales_manual_1_prt_mar2022_2_prt_europe DEFAULT;
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_europe_3_prt_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_europe FOR VALUES IN('f');
CREATE TABLE sales_manual_1_prt_mar2022_2_prt_europe_3_prt_non_fiction PARTITION OF sales_manual_1_prt_mar2022_2_prt_europe FOR VALUES IN('n');

Hash partitioning

The following example creates a table partitioned by the hash of the text column value. Each partition will contain the rows for which the hash divided by the specified modulus produces the specified remainder.

CREATE TABLE hash_test (a int, b int, c text) PARTITION BY HASH(c);
CREATE TABLE hash_test_1_prt_option1 PARTITION OF hash_test FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE hash_test_1_prt_option2 PARTITION OF hash_test FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE hash_test_1_prt_option3 PARTITION OF hash_test FOR VALUES WITH (MODULUS 3, REMAINDER 2);

Internal details

Regardless of the partitioning type, Greengage DB creates a top-level (or parent) table with one or more levels of subtables (or child tables). Internally, there is an inheritance relationship between the parent table and its underlying partitions, similar to the functionality of the PostgreSQL INHERITS clause. If you use psql, you can run \dt or \dt+ commands to list all relations and make sure that all created partitions are listed as regular tables. Notice that starting with ADB 7, root partitioned tables and non-leaf partitions are marked as partitioned table in the psql output, and leaf partitions are marked as table. In ADB 6, all partitions have the same type — table.

Top-level parent tables are always empty. Data is loaded to the bottom-level (leaf) partitions. In a multi-level partition design, only the subpartitions located at the hierarchy bottom contain data.

To limit the data that each partition can contain, Greengage DB uses the CHECK constraints. Rows that cannot be mapped to any child table partition are rejected and the load fails (unless the default partition is specified). The query optimizer also uses CHECK constraints to determine which partitions to scan according to the given query predicate.

To insert data into a partitioned table, you should specify the parent table created via the CREATE TABLE command. You can also specify a leaf partition directly. Starting with ADB 7, you can also insert data into non-leaf and non-root partitions.

For example, you can insert data into the sales_manual table created above (see Multi-level partitioning (manually)):

INSERT INTO sales_manual(id, date, fiction, region)
VALUES(1, date '2022-01-01', 'f', 'asia');

You can also insert new data into the leaf partition sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction:

INSERT INTO sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction(id, date, fiction, region)
VALUES(2, date '2022-01-02', 'f', 'asia');

Starting with ADB 7, you can insert rows even into non-leaf partitions, for example sales_manual_1_prt_jan2022:

INSERT INTO sales_manual_1_prt_jan2022(id, date, fiction, region) VALUES(3, date '2022-01-03', 'f', 'asia');

In ADB 6, this query returns the error:

ERROR:  directly modifying intermediate part of a partitioned table is disallowed
HINT:  Modify either the root or a leaf partition instead.

You cannot insert into partitions the data that does not meet the partition specification:

INSERT INTO sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction(id, date, fiction, region)
VALUES(4, date '2022-01-02', 'n', 'asia');

Result:

ERROR:  new row for relation "sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction" violates partition constraint  (seg0 10.92.41.82:10000 pid=4735)
DETAIL:  Failing row contains (4, 2022-01-02, n, asia).

To select data from the partitioned table, you can use either the parent table name or the leaf partition name:

SELECT * FROM  sales_manual;
SELECT * FROM  sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction;

Both queries return the same result:

 id |    date    | fiction | region
----+------------+---------+--------
  3 | 2022-01-03 | f       | asia
  1 | 2022-01-01 | f       | asia
  2 | 2022-01-02 | f       | asia
(3 rows)

Partition an existing table

You can partition tables only at creation. If you need to partition an existing table, you should create a new partitioned table and load data into it from the original table. The example is listed below:

Create an original table without partitioning:

CREATE TABLE genre (id INT, name TEXT NOT NULL, fiction CHAR(1) NOT NULL)
DISTRIBUTED BY (id);

Create a new partitioned table with the same structure using the LIKE clause:

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

CREATE TABLE genre_partitioned (LIKE genre)
PARTITION BY LIST (fiction)
(PARTITION fiction VALUES ('F'),
 PARTITION non_fiction VALUES ('N'),
 DEFAULT PARTITION other
);

CREATE TABLE genre_partitioned (LIKE genre)
PARTITION BY LIST (fiction);
CREATE TABLE genre_partitioned_1_prt_other PARTITION OF genre_partitioned DEFAULT;
CREATE TABLE genre_partitioned_1_prt_fiction PARTITION OF genre_partitioned FOR VALUES IN('F');
CREATE TABLE genre_partitioned_1_prt_non_fiction PARTITION OF genre_partitioned FOR VALUES IN('N');

IMPORTANT

Partition structures are not copied when you create a new table via the LIKE clause.

Load the original table data into the new table:

INSERT INTO genre_partitioned SELECT * FROM genre;

Drop the original table:
```
DROP TABLE genre;
```
Assign the original table name to the partitioned table:
```
ALTER TABLE genre_partitioned RENAME TO genre;
```
Re-grant all necessary permissions for the partitioned table:
```
GRANT ALL PRIVILEGES ON genre TO <role_name>;
```

View partitioned tables

To get information on partitioned tables in the current database, use one of the following ways:

To view all partitioned tables, you can run the SQL query against the pg_partitioned_table table:

SELECT
  pg_class.relname AS partition_table_name,
  pg_attribute.attname AS column_in_partition_key,
  class2.relname AS default_partition,
  pg_partitioned_table.partstrat AS partition_type
FROM pg_partitioned_table
INNER JOIN pg_class ON pg_class.oid = pg_partitioned_table.partrelid
INNER JOIN pg_attribute ON pg_attribute.attnum IN (SELECT unnest(pg_partitioned_table.partattrs)) AND pg_attribute.attrelid = pg_class.oid
LEFT JOIN pg_class class2 ON class2.oid = pg_partitioned_table.partdefid  ORDER BY pg_class.relname;

The command returns the following columns:

partition_table_name — object table name that is used to access the partition directly in DML commands (like INSERT, COPY, SELECT, etc.);
column_in_partition_key — column used as a partition key. If the partition key includes multiple columns, the query result will contain one row per each key column.
default_partition — name of the default partition (if any).
partition_type — partition type:
- h — hash;
- l — list;
- r — range.

Notice that leaf partitions are not included into the query result.

Result

            partition_table_name            | column_in_partition_key |                     default_partition                     | partition_type
--------------------------------------------+-------------------------+-----------------------------------------------------------+----------------
 book                                       | public_year             | book_1_prt_other                                          | r
 book_manual                                | public_year             | book_manual_1_prt_other                                   | r
 book_order                                 | order_date              | book_order_1_prt_other                                    | r
 book_order_manual                          | order_date              | book_order_manual_1_prt_other                             | r
 client                                     | gender                  | client_1_prt_other                                        | l
 sales                                      | date                    | sales_1_prt_other_dt                                      | r
 sales_1_prt_2                              | region                  | sales_1_prt_2_2_prt_other_rg                              | l
 sales_1_prt_2_2_prt_asia                   | fiction                 | sales_1_prt_2_2_prt_asia_3_prt_other_tp                   | l
 sales_1_prt_2_2_prt_europe                 | fiction                 | sales_1_prt_2_2_prt_europe_3_prt_other_tp                 | l
 sales_1_prt_2_2_prt_other_rg               | fiction                 | sales_1_prt_2_2_prt_other_rg_3_prt_other_tp               | l
 sales_1_prt_2_2_prt_rus                    | fiction                 | sales_1_prt_2_2_prt_rus_3_prt_other_tp                    | l
 sales_1_prt_3                              | region                  | sales_1_prt_3_2_prt_other_rg                              | l
 sales_1_prt_3_2_prt_asia                   | fiction                 | sales_1_prt_3_2_prt_asia_3_prt_other_tp                   | l
 sales_1_prt_3_2_prt_europe                 | fiction                 | sales_1_prt_3_2_prt_europe_3_prt_other_tp                 | l
 sales_1_prt_3_2_prt_other_rg               | fiction                 | sales_1_prt_3_2_prt_other_rg_3_prt_other_tp               | l
 sales_1_prt_3_2_prt_rus                    | fiction                 | sales_1_prt_3_2_prt_rus_3_prt_other_tp                    | l
 sales_1_prt_4                              | region                  | sales_1_prt_4_2_prt_other_rg                              | l
 sales_1_prt_4_2_prt_asia                   | fiction                 | sales_1_prt_4_2_prt_asia_3_prt_other_tp                   | l
 sales_1_prt_4_2_prt_europe                 | fiction                 | sales_1_prt_4_2_prt_europe_3_prt_other_tp                 | l
 sales_1_prt_4_2_prt_other_rg               | fiction                 | sales_1_prt_4_2_prt_other_rg_3_prt_other_tp               | l
 sales_1_prt_4_2_prt_rus                    | fiction                 | sales_1_prt_4_2_prt_rus_3_prt_other_tp                    | l
 sales_1_prt_other_dt                       | region                  | sales_1_prt_other_dt_2_prt_other_rg                       | l
 sales_1_prt_other_dt_2_prt_asia            | fiction                 | sales_1_prt_other_dt_2_prt_asia_3_prt_other_tp            | l
 sales_1_prt_other_dt_2_prt_europe          | fiction                 | sales_1_prt_other_dt_2_prt_europe_3_prt_other_tp          | l
 sales_1_prt_other_dt_2_prt_other_rg        | fiction                 | sales_1_prt_other_dt_2_prt_other_rg_3_prt_other_tp        | l
 sales_1_prt_other_dt_2_prt_rus             | fiction                 | sales_1_prt_other_dt_2_prt_rus_3_prt_other_tp             | l
 sales_manual                               | date                    | sales_manual_1_prt_other_dt                               | r
 sales_manual_1_prt_feb2022                 | region                  | sales_manual_1_prt_feb2022_2_prt_other_rg                 | l
 sales_manual_1_prt_feb2022_2_prt_asia      | fiction                 | sales_manual_1_prt_feb2022_2_prt_asia_3_prt_other_tp      | l
 sales_manual_1_prt_feb2022_2_prt_europe    | fiction                 | sales_manual_1_prt_feb2022_2_prt_europe_3_prt_other_tp    | l
 sales_manual_1_prt_feb2022_2_prt_other_rg  | fiction                 | sales_manual_1_prt_feb2022_2_prt_other_rg_3_prt_other_tp  | l
 sales_manual_1_prt_feb2022_2_prt_rus       | fiction                 | sales_manual_1_prt_feb2022_2_prt_rus_3_prt_other_tp       | l
 sales_manual_1_prt_jan2022                 | region                  | sales_manual_1_prt_jan2022_2_prt_other_rg                 | l
 sales_manual_1_prt_jan2022_2_prt_asia      | fiction                 | sales_manual_1_prt_jan2022_2_prt_asia_3_prt_other_tp      | l
 sales_manual_1_prt_jan2022_2_prt_europe    | fiction                 | sales_manual_1_prt_jan2022_2_prt_europe_3_prt_other_tp    | l
 sales_manual_1_prt_jan2022_2_prt_other_rg  | fiction                 | sales_manual_1_prt_jan2022_2_prt_other_rg_3_prt_other_tp  | l
 sales_manual_1_prt_jan2022_2_prt_rus       | fiction                 | sales_manual_1_prt_jan2022_2_prt_rus_3_prt_other_tp       | l
 sales_manual_1_prt_mar2022                 | region                  | sales_manual_1_prt_mar2022_2_prt_other_rg                 | l
 sales_manual_1_prt_mar2022_2_prt_asia      | fiction                 | sales_manual_1_prt_mar2022_2_prt_asia_3_prt_other_tp      | l
 sales_manual_1_prt_mar2022_2_prt_europe    | fiction                 | sales_manual_1_prt_mar2022_2_prt_europe_3_prt_other_tp    | l
 sales_manual_1_prt_mar2022_2_prt_other_rg  | fiction                 | sales_manual_1_prt_mar2022_2_prt_other_rg_3_prt_other_tp  | l
 sales_manual_1_prt_mar2022_2_prt_rus       | fiction                 | sales_manual_1_prt_mar2022_2_prt_rus_3_prt_other_tp       | l
 sales_manual_1_prt_other_dt                | region                  | sales_manual_1_prt_other_dt_2_prt_other_rg                | l
 sales_manual_1_prt_other_dt_2_prt_asia     | fiction                 | sales_manual_1_prt_other_dt_2_prt_asia_3_prt_other_tp     | l
 sales_manual_1_prt_other_dt_2_prt_europe   | fiction                 | sales_manual_1_prt_other_dt_2_prt_europe_3_prt_other_tp   | l
 sales_manual_1_prt_other_dt_2_prt_other_rg | fiction                 | sales_manual_1_prt_other_dt_2_prt_other_rg_3_prt_other_tp | l
 sales_manual_1_prt_other_dt_2_prt_rus      | fiction                 | sales_manual_1_prt_other_dt_2_prt_rus_3_prt_other_tp      | l
(47 rows)

To view the partition design of the specified table with all partitions, you can call the pg_partition_tree function. As the <partition_table_name> argument value, you can use a name of the top-level table or any of its partitions. The partition design is shown for all lower levels in the hierarchy:

SELECT * from pg_partition_tree('<partition_table_name>');

The command returns the following columns:

relid — object table name that is used to access the partition directly in DML commands (like INSERT, COPY, SELECT, etc.).
parentrelid — parent table name.
isleaf — whether the partition is leaf:
- t — true;
- f — false.
level — partition level in the hierarchy. Takes the value 0 for the partition specified as the <partition_table_name> function argument (even if this partition is non-root), 1 — for its subpartitions, 2 — for their subpartitions, and so on.

Example for a root partition

SELECT * FROM pg_partition_tree('book');

Result:

      relid       | parentrelid | isleaf | level
------------------+-------------+--------+-------
 book             |             | f      |     0
 book_1_prt_other | book        | t      |     1
 book_1_prt_2     | book        | t      |     1
 book_1_prt_3     | book        | t      |     1
 book_1_prt_4     | book        | t      |     1
 book_1_prt_5     | book        | t      |     1
 book_1_prt_6     | book        | t      |     1
 book_1_prt_7     | book        | t      |     1
 book_1_prt_8     | book        | t      |     1
 book_1_prt_9     | book        | t      |     1
 book_1_prt_10    | book        | t      |     1
 book_1_prt_11    | book        | t      |     1
(12 rows)

Example for an intermediate partition

SELECT * FROM pg_partition_tree('sales_1_prt_4_2_prt_asia');

Result:

                   relid                    |       parentrelid        | isleaf | level
--------------------------------------------+--------------------------+--------+-------
 sales_1_prt_4_2_prt_asia                   | sales_1_prt_4            | f      |     0
 sales_1_prt_4_2_prt_asia_3_prt_other_tp    | sales_1_prt_4_2_prt_asia | t      |     1
 sales_1_prt_4_2_prt_asia_3_prt_fiction     | sales_1_prt_4_2_prt_asia | t      |     1
 sales_1_prt_4_2_prt_asia_3_prt_non_fiction | sales_1_prt_4_2_prt_asia | t      |     1
(4 rows)

To view all tables that are parents for the specified partition in the partitioning hierarchy, you can call the pg_partition_ancestors function. As the <partition_table_name> argument value, use the partition name:
```
SELECT * from pg_partition_ancestors('<partition_table_name>');
```
The query returns only one column relid, which shows names of all parent partitions for the specified one in the partitioning hierarchy. The specified partition is shown as well.
```
SELECT * FROM pg_partition_ancestors(' sales_1_prt_4_2_prt_asia');
```
Result:
```
          relid
--------------------------
 sales_1_prt_4_2_prt_asia
 sales_1_prt_4
 sales
(3 rows)
```
To view the top-level (root) table for the specified partition in the partitioning hierarchy, you can call the pg_partition_root function. As the <partition_table_name> argument value, use the partition name:
```
SELECT * from pg_partition_root('<partition_table_name>');
```
The query returns one value in the pg_partition_root column, which is the name of the root table.

Example:
```
SELECT * FROM pg_partition_root(' sales_1_prt_4_2_prt_asia');
```
Result:
```
 pg_partition_root
-------------------
 sales
(1 row)
```

Operations with partitions

Add a new partition

To add a new partition to the partitioned table, you can use the ALTER TABLE command with the ADD PARTITION clause. Starting with ADB 7 you can also define a new partition using the CREATE TABLE <partition_table_name> PARTITION OF <parent_table_name> command or attach an existing regular table as a partition using the ALTER TABLE <parent_table_name> ATTACH PARTITION <partition_table_name> command.

The following example creates a new partitioned table and adds some partitions to it:

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

Create a two-level partitioned table without default partitions:

CREATE TABLE genre_stat
  (id SERIAL,
   genre text NOT NULL,
   year INT NOT NULL,
   count INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE (year)
 SUBPARTITION BY LIST (genre)
  SUBPARTITION TEMPLATE (
    SUBPARTITION poetry VALUES ('poetry'),
    SUBPARTITION fantasy VALUES ('fantasy'),
    SUBPARTITION detective VALUES ('detective')
  )
(START(2013)
 END(2015)
 EVERY(1));

Partition design:

SELECT * FROM pg_partition_tree('genre_stat');

               relid                |    parentrelid     | isleaf | level
------------------------------------+--------------------+--------+-------
 genre_stat                         |                    | f      |     0
 genre_stat_1_prt_1                 | genre_stat         | f      |     1
 genre_stat_1_prt_2                 | genre_stat         | f      |     1
 genre_stat_1_prt_1_2_prt_poetry    | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy   | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_detective | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_2_2_prt_poetry    | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy   | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_detective | genre_stat_1_prt_2 | t      |     2
(9 rows)

Add a new partition using the following query:

ALTER TABLE genre_stat ADD PARTITION START(2012) END(2013);

The new partition design is shown below. Since the parent table was created using a template, the new partition is divided into subpartitions according to that template. Otherwise it would have been necessary to manually describe each subpartition for a new partition.

                relid                |     parentrelid     | isleaf | level
-------------------------------------+---------------------+--------+-------
 genre_stat                          |                     | f      |     0
 genre_stat_1_prt_1                  | genre_stat          | f      |     1
 genre_stat_1_prt_2                  | genre_stat          | f      |     1
 genre_stat_1_prt_11                 | genre_stat          | f      |     1
 genre_stat_1_prt_1_2_prt_poetry     | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy    | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_detective  | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_2_2_prt_poetry     | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy    | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_detective  | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_11_2_prt_poetry    | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_fantasy   | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_detective | genre_stat_1_prt_11 | t      |     2
(13 rows)

You can also add subpartitions to existing partitions. To do this, define the full path to the specific partition via ALTER PARTITION clauses — one clause for each partition level above the target partition:

ALTER TABLE genre_stat ALTER PARTITION FOR(2012)
      ADD PARTITION fantastic VALUES ('fantastic');

Updated partition design:

                relid                |     parentrelid     | isleaf | level
-------------------------------------+---------------------+--------+-------
 genre_stat                          |                     | f      |     0
 genre_stat_1_prt_1                  | genre_stat          | f      |     1
 genre_stat_1_prt_2                  | genre_stat          | f      |     1
 genre_stat_1_prt_11                 | genre_stat          | f      |     1
 genre_stat_1_prt_1_2_prt_poetry     | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy    | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_detective  | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_2_2_prt_poetry     | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy    | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_detective  | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_11_2_prt_poetry    | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_fantasy   | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_detective | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_fantastic | genre_stat_1_prt_11 | t      |     2
(14 rows)

Create a two-level partitioned table without default partitions:

CREATE TABLE genre_stat
  (id SERIAL,
   genre text NOT NULL,
   year INT NOT NULL,
   count INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE (year);
CREATE TABLE genre_stat_1_prt_1 PARTITION OF genre_stat FOR VALUES FROM (2013) TO (2014) PARTITION BY LIST (genre);
CREATE TABLE genre_stat_1_prt_2 PARTITION OF genre_stat FOR VALUES FROM (2014) TO (2015) PARTITION BY LIST (genre);
CREATE TABLE genre_stat_1_prt_1_2_prt_poetry PARTITION OF genre_stat_1_prt_1 FOR VALUES IN('poetry');
CREATE TABLE genre_stat_1_prt_1_2_prt_fantasy PARTITION OF genre_stat_1_prt_1 FOR VALUES IN('fantasy');
CREATE TABLE genre_stat_1_prt_1_2_prt_detective PARTITION OF genre_stat_1_prt_1 FOR VALUES IN('detective');
CREATE TABLE genre_stat_1_prt_2_2_prt_poetry PARTITION OF genre_stat_1_prt_2 FOR VALUES IN('poetry');
CREATE TABLE genre_stat_1_prt_2_2_prt_fantasy PARTITION OF genre_stat_1_prt_2 FOR VALUES IN('fantasy');
CREATE TABLE genre_stat_1_prt_2_2_prt_detective PARTITION OF genre_stat_1_prt_2 FOR VALUES IN('detective');

Partition design:

SELECT * FROM pg_partition_tree('genre_stat');

               relid                |    parentrelid     | isleaf | level
------------------------------------+--------------------+--------+-------
 genre_stat                         |                    | f      |     0
 genre_stat_1_prt_1                 | genre_stat         | f      |     1
 genre_stat_1_prt_2                 | genre_stat         | f      |     1
 genre_stat_1_prt_1_2_prt_poetry    | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy   | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_detective | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_2_2_prt_poetry    | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy   | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_detective | genre_stat_1_prt_2 | t      |     2
(9 rows)

Add a new partition using the following queries. Notice that you should add all subpartitions for a new partition manually (similar to creating the original partitioned table):

CREATE TABLE genre_stat_1_prt_3 PARTITION OF genre_stat FOR VALUES FROM (2012) TO (2013) PARTITION BY LIST (genre);
CREATE TABLE genre_stat_1_prt_3_2_prt_poetry PARTITION OF genre_stat_1_prt_3 FOR VALUES IN('poetry');
CREATE TABLE genre_stat_1_prt_3_2_prt_fantasy PARTITION OF genre_stat_1_prt_3 FOR VALUES IN('fantasy');
CREATE TABLE genre_stat_1_prt_3_2_prt_detective PARTITION OF genre_stat_1_prt_3 FOR VALUES IN('detective');

The new partition design is shown below:

               relid                |    parentrelid     | isleaf | level
------------------------------------+--------------------+--------+-------
 genre_stat                         |                    | f      |     0
 genre_stat_1_prt_1                 | genre_stat         | f      |     1
 genre_stat_1_prt_2                 | genre_stat         | f      |     1
 genre_stat_1_prt_3                 | genre_stat         | f      |     1
 genre_stat_1_prt_1_2_prt_poetry    | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy   | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_detective | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_2_2_prt_poetry    | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy   | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_detective | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_3_2_prt_poetry    | genre_stat_1_prt_3 | t      |     2
 genre_stat_1_prt_3_2_prt_fantasy   | genre_stat_1_prt_3 | t      |     2
 genre_stat_1_prt_3_2_prt_detective | genre_stat_1_prt_3 | t      |     2
(13 rows)

You can also add subpartitions to existing partitions:

CREATE TABLE genre_stat_1_prt_11_2_prt_fantastic PARTITION OF genre_stat_1_prt_3 FOR VALUES IN('fantastic');

Updated partition design:

                relid                |    parentrelid     | isleaf | level
-------------------------------------+--------------------+--------+-------
 genre_stat                          |                    | f      |     0
 genre_stat_1_prt_1                  | genre_stat         | f      |     1
 genre_stat_1_prt_2                  | genre_stat         | f      |     1
 genre_stat_1_prt_3                  | genre_stat         | f      |     1
 genre_stat_1_prt_1_2_prt_poetry     | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy    | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_1_2_prt_detective  | genre_stat_1_prt_1 | t      |     2
 genre_stat_1_prt_2_2_prt_poetry     | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy    | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_2_2_prt_detective  | genre_stat_1_prt_2 | t      |     2
 genre_stat_1_prt_3_2_prt_poetry     | genre_stat_1_prt_3 | t      |     2
 genre_stat_1_prt_3_2_prt_fantasy    | genre_stat_1_prt_3 | t      |     2
 genre_stat_1_prt_3_2_prt_detective  | genre_stat_1_prt_3 | t      |     2
 genre_stat_1_prt_11_2_prt_fantastic | genre_stat_1_prt_3 | t      |     2
(14 rows)

Rename a partition

To change a partition name, you can use the ALTER TABLE command with the RENAME PARTITION clause. Starting with ADB 7, you can also rename partitions directly — in the same way you rename a table, using the ALTER TABLE … RENAME TO command.

The following example renames the book_1_prt_2 partition in the parent table book created above (see Numeric range partitioning (automatically)).

Check the current partition name:

SELECT * FROM pg_partition_tree('book');

The result is listed below. One of partitions has the name book_1_prt_2:

      relid       | parentrelid | isleaf | level
------------------+-------------+--------+-------
 book             |             | f      |     0
 book_1_prt_other | book        | t      |     1
 book_1_prt_2     | book        | t      |     1
 book_1_prt_3     | book        | t      |     1
 book_1_prt_4     | book        | t      |     1
 book_1_prt_5     | book        | t      |     1
 book_1_prt_6     | book        | t      |     1
 book_1_prt_7     | book        | t      |     1
 book_1_prt_8     | book        | t      |     1
 book_1_prt_9     | book        | t      |     1
 book_1_prt_10    | book        | t      |     1
 book_1_prt_11    | book        | t      |     1
(12 rows)

Rename the partition as follows:

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

ALTER TABLE book RENAME PARTITION FOR(2013) TO year2013;

ALTER TABLE book_1_prt_2 RENAME TO book_1_prt_year2013;

Check the partition name again (using the query from step 1). You can see that the relid value has changed (book_1_prt_year2013 instead of book_1_prt_2):

        relid        | parentrelid | isleaf | level
---------------------+-------------+--------+-------
 book                |             | f      |     0
 book_1_prt_other    | book        | t      |     1
 book_1_prt_year2013 | book        | t      |     1
 book_1_prt_3        | book        | t      |     1
 book_1_prt_4        | book        | t      |     1
 book_1_prt_5        | book        | t      |     1
 book_1_prt_6        | book        | t      |     1
 book_1_prt_7        | book        | t      |     1
 book_1_prt_8        | book        | t      |     1
 book_1_prt_9        | book        | t      |     1
 book_1_prt_10       | book        | t      |     1
 book_1_prt_11       | book        | t      |     1
(12 rows)

Notice that changes in the parent table name are also propagated to the child partitions. For example, the following command renames the parent table book to magazine:

ALTER TABLE book RENAME TO magazine;

The command applies changes to all table partitions as well. You can check it via the following query:

SELECT * FROM pg_partition_tree('magazine');

Result:

          relid          | parentrelid | isleaf | level
-------------------------+-------------+--------+-------
 magazine                |             | f      |     0
 magazine_1_prt_other    | magazine    | t      |     1
 magazine_1_prt_year2013 | magazine    | t      |     1
 magazine_1_prt_3        | magazine    | t      |     1
 magazine_1_prt_4        | magazine    | t      |     1
 magazine_1_prt_5        | magazine    | t      |     1
 magazine_1_prt_6        | magazine    | t      |     1
 magazine_1_prt_7        | magazine    | t      |     1
 magazine_1_prt_8        | magazine    | t      |     1
 magazine_1_prt_9        | magazine    | t      |     1
 magazine_1_prt_10       | magazine    | t      |     1
 magazine_1_prt_11       | magazine    | t      |     1
(12 rows)

Add a default partition

To add a default partition to an existing partitioned table, you can use the ALTER TABLE command with the ADD DEFAULT PARTITION clause. Starting with ADB 7, you can also define a new partition as default (using the CREATE TABLE <partition_table_name> PARTITION OF <parent_table_name> DEFAULT; command) or attach an existing regular table as a default partition (using the ALTER TABLE <parent_table_name> ATTACH PARTITION <partition_table_name> DEFAULT command).

The following example adds a default partition to the genre_stat table created above (see Add a new partition) at the first level of the partition design:

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

ALTER TABLE genre_stat ADD DEFAULT PARTITION other;

The new partition design is shown below. All subpartitions are created according to the subpartition template that you used when creating a table.

SELECT * FROM pg_partition_tree('genre_stat');

Result:

                 relid                  |      parentrelid       | isleaf | level
----------------------------------------+------------------------+--------+-------
 genre_stat                             |                        | f      |     0
 genre_stat_1_prt_1                     | genre_stat             | f      |     1
 genre_stat_1_prt_2                     | genre_stat             | f      |     1
 genre_stat_1_prt_11                    | genre_stat             | f      |     1
 genre_stat_1_prt_other                 | genre_stat             | f      |     1
 genre_stat_1_prt_1_2_prt_poetry        | genre_stat_1_prt_1     | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy       | genre_stat_1_prt_1     | t      |     2
 genre_stat_1_prt_1_2_prt_detective     | genre_stat_1_prt_1     | t      |     2
 genre_stat_1_prt_2_2_prt_poetry        | genre_stat_1_prt_2     | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy       | genre_stat_1_prt_2     | t      |     2
 genre_stat_1_prt_2_2_prt_detective     | genre_stat_1_prt_2     | t      |     2
 genre_stat_1_prt_11_2_prt_poetry       | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_11_2_prt_fantasy      | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_11_2_prt_detective    | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_11_2_prt_fantastic    | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_other_2_prt_poetry    | genre_stat_1_prt_other | t      |     2
 genre_stat_1_prt_other_2_prt_fantasy   | genre_stat_1_prt_other | t      |     2
 genre_stat_1_prt_other_2_prt_detective | genre_stat_1_prt_other | t      |     2
(18 rows)

CREATE TABLE genre_stat_1_prt_other PARTITION OF genre_stat DEFAULT;

Alternatively, you can run the following commands:

CREATE TABLE genre_stat_1_prt_other (LIKE genre_stat);
ALTER TABLE genre_stat ATTACH PARTITION genre_stat_1_prt_other DEFAULT;

The new partition design is shown below. Notice that unlike the classic syntax example, no subpartitions are created automatically. If necessary, create subpartitions for the default partition manually.

SELECT * FROM pg_partition_tree('genre_stat');

Result:

                relid                |     parentrelid     | isleaf | level
-------------------------------------+---------------------+--------+-------
 genre_stat                          |                     | f      |     0
 genre_stat_1_prt_1                  | genre_stat          | f      |     1
 genre_stat_1_prt_2                  | genre_stat          | f      |     1
 genre_stat_1_prt_11                 | genre_stat          | f      |     1
 genre_stat_1_prt_other              | genre_stat          | t      |     1
 genre_stat_1_prt_1_2_prt_poetry     | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy    | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_detective  | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_2_2_prt_poetry     | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy    | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_detective  | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_11_2_prt_poetry    | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_fantasy   | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_detective | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_fantastic | genre_stat_1_prt_11 | t      |     2
(15 rows)

You can also add default partitions to other hierarchy levels. The following query creates a default partition at the second hierarchy level:

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

Notice that you should define the full path to the specific partition via ALTER PARTITION clauses — one clause for each partition level above the target default partition.

ALTER TABLE genre_stat ALTER PARTITION FOR (2013) ADD DEFAULT PARTITION other;

Updated partition design:

                 relid                  |      parentrelid       | isleaf | level
----------------------------------------+------------------------+--------+-------
 genre_stat                             |                        | f      |     0
 genre_stat_1_prt_1                     | genre_stat             | f      |     1
 genre_stat_1_prt_2                     | genre_stat             | f      |     1
 genre_stat_1_prt_11                    | genre_stat             | f      |     1
 genre_stat_1_prt_other                 | genre_stat             | f      |     1
 genre_stat_1_prt_1_2_prt_poetry        | genre_stat_1_prt_1     | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy       | genre_stat_1_prt_1     | t      |     2
 genre_stat_1_prt_1_2_prt_detective     | genre_stat_1_prt_1     | t      |     2
 genre_stat_1_prt_1_2_prt_other         | genre_stat_1_prt_1     | t      |     2
 genre_stat_1_prt_2_2_prt_poetry        | genre_stat_1_prt_2     | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy       | genre_stat_1_prt_2     | t      |     2
 genre_stat_1_prt_2_2_prt_detective     | genre_stat_1_prt_2     | t      |     2
 genre_stat_1_prt_11_2_prt_poetry       | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_11_2_prt_fantasy      | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_11_2_prt_detective    | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_11_2_prt_fantastic    | genre_stat_1_prt_11    | t      |     2
 genre_stat_1_prt_other_2_prt_poetry    | genre_stat_1_prt_other | t      |     2
 genre_stat_1_prt_other_2_prt_fantasy   | genre_stat_1_prt_other | t      |     2
 genre_stat_1_prt_other_2_prt_detective | genre_stat_1_prt_other | t      |     2
(19 rows)

CREATE TABLE genre_stat_1_prt_1_2_prt_other PARTITION OF genre_stat_1_prt_1 DEFAULT;

Updated partition design:

                relid                |     parentrelid     | isleaf | level
-------------------------------------+---------------------+--------+-------
 genre_stat                          |                     | f      |     0
 genre_stat_1_prt_1                  | genre_stat          | f      |     1
 genre_stat_1_prt_2                  | genre_stat          | f      |     1
 genre_stat_1_prt_11                 | genre_stat          | f      |     1
 genre_stat_1_prt_other              | genre_stat          | t      |     1
 genre_stat_1_prt_1_2_prt_poetry     | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_fantasy    | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_detective  | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_1_2_prt_other      | genre_stat_1_prt_1  | t      |     2
 genre_stat_1_prt_2_2_prt_poetry     | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_fantasy    | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_2_2_prt_detective  | genre_stat_1_prt_2  | t      |     2
 genre_stat_1_prt_11_2_prt_poetry    | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_fantasy   | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_detective | genre_stat_1_prt_11 | t      |     2
 genre_stat_1_prt_11_2_prt_fantastic | genre_stat_1_prt_11 | t      |     2
(16 rows)

Split a partition

To divide a partition into two partitions, you can use the ALTER TABLE command with the SPLIT PARTITION clause.

The following example splits partitions of the book_order table created above (see Date range partitioning (automatically)). The command syntax is relevant both for ADB 6 and ADB 7:

Split the partition allocated for January 2022:

ALTER TABLE book_order SPLIT PARTITION FOR ('2022-01-01')
AT ('2022-01-16')
INTO (PARTITION jan1to15, PARTITION jan16to31);

The new partition design is shown below. The split value (2022-01-16) goes into the second partition. Therefore, the first partition contains dates from January 1 to 15, and the second partition — from January 16 to 31.

SELECT * FROM pg_partition_tree('book_order');

Result:

           relid            | parentrelid | isleaf | level
----------------------------+-------------+--------+-------
 book_order                 |             | f      |     0
 book_order_1_prt_other     | book_order  | t      |     1
 book_order_1_prt_3         | book_order  | t      |     1
 book_order_1_prt_4         | book_order  | t      |     1
 book_order_1_prt_5         | book_order  | t      |     1
 book_order_1_prt_6         | book_order  | t      |     1
 book_order_1_prt_7         | book_order  | t      |     1
 book_order_1_prt_8         | book_order  | t      |     1
 book_order_1_prt_9         | book_order  | t      |     1
 book_order_1_prt_10        | book_order  | t      |     1
 book_order_1_prt_11        | book_order  | t      |     1
 book_order_1_prt_12        | book_order  | t      |     1
 book_order_1_prt_13        | book_order  | t      |     1
 book_order_1_prt_jan1to15  | book_order  | t      |     1
 book_order_1_prt_jan16to31 | book_order  | t      |     1
(15 rows)

NOTE

In ADB 6, to add partitions at the same level with the default partition, you need to split the default partition at first (otherwise the attempt to add a new partition fails). In ADB 7, there is no such requirement.
You can split only the partitions that are located at the hierarchy bottom (leaf partitions).
For multi-level partitions, you can split only range partitions, not list ones.

Exchange a partition

Partition exchange allows you to replace a partition with a table having the same structure. This intermediate table is usually called a staging table. After the operation is applied, the staging table contains the partition data, and the partition contains the staging table data. This feature is especially useful for data loading, since inserting data into partitioned tables is inefficient. You can also use partition exchange to change the storage type of existing partitions.

To exchange a partition, you can use the ALTER TABLE command with the EXCHANGE PARTITION clause. Alternatively, starting with ADB 7, you can detach an original partition and then attach a new partition with required data.

The following example exchanges a partition of the book_order_manual table created above (see Date range partitioning (manually)):

List the parent table with all partitions via the psql \dt+ command:

\dt+ book_order_manual*

Result:

                                           List of relations
 Schema |             Name              |       Type        |  Owner  | Storage |  Size   | Description
--------+-------------------------------+-------------------+---------+---------+---------+-------------
 public | book_order_manual             | partitioned table | gpadmin | ao_row  | 0 bytes |
 public | book_order_manual_1_prt_apr22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_aug22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_dec22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_feb22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_jan22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_jul22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_jun22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_mar22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_may22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_nov22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_oct22 | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_other | table             | gpadmin | ao_row  | 288 kB  |
 public | book_order_manual_1_prt_sep22 | table             | gpadmin | ao_row  | 288 kB  |
(14 rows)

Check that the book_order_manual_1_prt_dec22 partition does not contain data:

SELECT * FROM book_order_manual_1_prt_dec22;

Result:

 id | book_id | client_id | book_count | order_date
----+---------+-----------+------------+------------
(0 rows)

Create a table with the same structure as the partitioned table book_order_manual has. Choose another storage type:
```
CREATE TABLE dec22 (LIKE book_order_manual) WITH (appendoptimized=false);
```

Insert some data into the new table:

INSERT INTO dec22(id, book_id, client_id, book_count, order_date)
VALUES(1, 1, 1, 1, '2022-12-01');

Exchange the book_order_manual_1_prt_dec22 partition with a new table.

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

ALTER TABLE book_order_manual EXCHANGE PARTITION FOR (DATE '2022-12-01')
WITH TABLE dec22;

ALTER TABLE book_order_manual DETACH PARTITION book_order_manual_1_prt_dec22;
ALTER TABLE book_order_manual ATTACH PARTITION dec22 FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');
ALTER TABLE book_order_manual_1_prt_dec22 RENAME TO detached;
ALTER TABLE dec22 RENAME TO book_order_manual_1_prt_dec22;

Check that the partition contains data (using the query from step 2):

SELECT * FROM book_order_manual_1_prt_dec22;

Result:

 id | book_id | client_id | book_count | order_date
----+---------+-----------+------------+------------
  1 |       1 |         1 |          1 | 2022-12-01
(1 row)

Check that the storage type of the partition has changed as well (using the psql command from step 1):

\dt+ book_order_manual*

Result:

                                           List of relations
 Schema |             Name              |       Type        |  Owner  | Storage |  Size   | Description
--------+-------------------------------+-------------------+---------+---------+---------+-------------
 public | book_order_manual             | partitioned table | gpadmin | ao_row  | 0 bytes |
 public | book_order_manual_1_prt_apr22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_aug22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_dec22 | table             | gpadmin | heap    | 32 kB   |
 public | book_order_manual_1_prt_feb22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_jan22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_jul22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_jun22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_mar22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_may22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_nov22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_oct22 | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_other | table             | gpadmin | ao_row  | 96 kB   |
 public | book_order_manual_1_prt_sep22 | table             | gpadmin | ao_row  | 96 kB   |
(14 rows)

NOTE

You can exchange only the partitions that are located at the hierarchy bottom (leaf partitions).
You cannot exchange partitions with partitioned tables or non-leaf partitions of partitioned tables.
You cannot exchange partitions with tables having the DISTRIBUTED REPLICATED distribution policy.
Ensure that the staging table data meets the partition specification requirements.
You can exchange partitions with external tables, yet with some limitations.

Truncate a partition

To delete all data from the specified partition without dropping the partition itself, you can use the ALTER TABLE command with the TRUNCATE PARTITION clause. Starting with ADB 7, you can also truncate partitions directly — in the same way you truncate a regular table, using the TRUNCATE command.

The following example truncates the partition sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction, to which we added the data above:

Check that the partition contains data:

SELECT * FROM sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction;

Result:

 id |    date    | fiction | region
----+------------+---------+--------
  3 | 2022-01-03 | f       | asia
  2 | 2022-01-02 | f       | asia
  1 | 2022-01-01 | f       | asia
(3 rows)

Truncate the partition.

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

Pay attention that for a multi-level partitioned table, you should identify the full path to the specific leaf partition via ALTER PARTITION clauses. Describe every partition level in the table hierarchy that is above the target partition:

ALTER TABLE sales_manual
ALTER PARTITION jan2022
ALTER PARTITION asia
TRUNCATE PARTITION fiction;

Truncate the specified partition only:

TRUNCATE ONLY sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction;

Truncate the whole partitioned table:

TRUNCATE sales_manual;

Check that the truncated partition does not contain data (using the query from step 1):

SELECT * FROM sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction;

Result:

 id | date | fiction | region
----+------+---------+--------
(0 rows)

NOTE

When you truncate a partition with subpartitions, the latter are automatically truncated as well.

Drop a partition

To drop the specified partition, you can use the ALTER TABLE command with the DROP PARTITION clause. Starting with ADB 7, you can also drop partitions directly — in the same way you drop a regular table, using the DROP TABLE command.

The following example drops the partition year2013 from the book_manual table created above (see Numeric range partitioning (manually)):

Check that the partition exists:

SELECT * FROM pg_partition_tree('book_manual');

Result:

           relid            | parentrelid | isleaf | level
----------------------------+-------------+--------+-------
 book_manual                |             | f      |     0
 book_manual_1_prt_other    | book_manual | t      |     1
 book_manual_1_prt_year2013 | book_manual | t      |     1
 book_manual_1_prt_year2014 | book_manual | t      |     1
 book_manual_1_prt_year2015 | book_manual | t      |     1
 book_manual_1_prt_year2016 | book_manual | t      |     1
 book_manual_1_prt_year2017 | book_manual | t      |     1
 book_manual_1_prt_year2018 | book_manual | t      |     1
 book_manual_1_prt_year2019 | book_manual | t      |     1
 book_manual_1_prt_year2020 | book_manual | t      |     1
 book_manual_1_prt_year2021 | book_manual | t      |     1
 book_manual_1_prt_year2022 | book_manual | t      |     1
(12 rows)

Drop the partition:

Classic syntax (ADB 6 and ADB 7)
Modern syntax (ADB 7 only)

ALTER TABLE book_manual DROP PARTITION year2013;

DROP TABLE book_manual_1_prt_year2013;

Check that the partition does not exist anymore (using the query from step 1):

           relid            | parentrelid | isleaf | level
----------------------------+-------------+--------+-------
 book_manual                |             | f      |     0
 book_manual_1_prt_other    | book_manual | t      |     1
 book_manual_1_prt_year2014 | book_manual | t      |     1
 book_manual_1_prt_year2015 | book_manual | t      |     1
 book_manual_1_prt_year2016 | book_manual | t      |     1
 book_manual_1_prt_year2017 | book_manual | t      |     1
 book_manual_1_prt_year2018 | book_manual | t      |     1
 book_manual_1_prt_year2019 | book_manual | t      |     1
 book_manual_1_prt_year2020 | book_manual | t      |     1
 book_manual_1_prt_year2021 | book_manual | t      |     1
 book_manual_1_prt_year2022 | book_manual | t      |     1
(11 rows)

NOTE

When you drop a partition with subpartitions, the latter are automatically dropped as well (with all data in them).

Detach a partition

Detaching a partition (available starting with ADB 7) removes it from the partition hierarchy, but does not drop the table itself. It can be useful for performing additional operations on data before dropping it. To detach a partition, use the ALTER TABLE … DETACH PARTITION command.

The following example detaches the book_manual_1_prt_year2021 partition from the book_manual table created above (see Numeric range partitioning (manually)):

Check that the partition exists:

SELECT * FROM pg_partition_tree('book_manual');

Result:

           relid            | parentrelid | isleaf | level
----------------------------+-------------+--------+-------
 book_manual                |             | f      |     0
 book_manual_1_prt_other    | book_manual | t      |     1
 book_manual_1_prt_year2014 | book_manual | t      |     1
 book_manual_1_prt_year2015 | book_manual | t      |     1
 book_manual_1_prt_year2016 | book_manual | t      |     1
 book_manual_1_prt_year2017 | book_manual | t      |     1
 book_manual_1_prt_year2018 | book_manual | t      |     1
 book_manual_1_prt_year2019 | book_manual | t      |     1
 book_manual_1_prt_year2020 | book_manual | t      |     1
 book_manual_1_prt_year2021 | book_manual | t      |     1
 book_manual_1_prt_year2022 | book_manual | t      |     1
(11 rows)

Detach the partition:

ALTER TABLE book_manual DETACH PARTITION book_manual_1_prt_year2021;

Check that the partition is removed from the partition hierarchy (using the query from step 1):

           relid            | parentrelid | isleaf | level
----------------------------+-------------+--------+-------
 book_manual                |             | f      |     0
 book_manual_1_prt_other    | book_manual | t      |     1
 book_manual_1_prt_year2014 | book_manual | t      |     1
 book_manual_1_prt_year2015 | book_manual | t      |     1
 book_manual_1_prt_year2016 | book_manual | t      |     1
 book_manual_1_prt_year2017 | book_manual | t      |     1
 book_manual_1_prt_year2018 | book_manual | t      |     1
 book_manual_1_prt_year2019 | book_manual | t      |     1
 book_manual_1_prt_year2020 | book_manual | t      |     1
 book_manual_1_prt_year2022 | book_manual | t      |     1
(10 rows)

Ensure that the table still exists:
```
\dt+ book_manual*
```
Result:

                                          List of relations
 Schema |            Name            |       Type        |  Owner  | Storage |  Size   | Description
--------+----------------------------+-------------------+---------+---------+---------+-------------
 public | book_manual                | partitioned table | gpadmin | ao_row  | 0 bytes |
 public | book_manual_1_prt_other    | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2014 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2015 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2016 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2017 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2018 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2019 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2020 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2021 | table             | gpadmin | ao_row  | 192 kB  |
 public | book_manual_1_prt_year2022 | table             | gpadmin | ao_row  | 192 kB  |
(11 rows)

NOTE

When you detach a partition with subpartitions, the latter are automatically detached as well.

Found a mistake? Seleсt text and press Ctrl+Enter to report it