Partitioned table

A table divided into partitions using declarative partitioning

A partitioned table is a special kind of table which has been divided into partitions using one of the methods provided by declarative partitioning. It is not possible to insert data into a partitioned table itself; all inserts must be able to be routed into one of its partitions.

Partitioned tables have been present since PostgreSQL 10, when declarative paritioning was added.

System catalog

When a partitioned table is created, an entry is created in the following system catalogue tables:

Examples

Creating the sample table from the PostgreSQL partitioning documentation:

postgres=# CREATE TABLE measurement (
               city_id         INT NOT NULL,
               logdate         DATE NOT NULL,
               peaktemp        INT,
               unitsales       INT
           ) PARTITION BY RANGE (logdate);
CREATE TABLE

postgres=# \dt
                  List of relations
 Schema |    Name     |       Type        |  Owner
--------+-------------+-------------------+----------
 public | measurement | partitioned table | postgres
(1 row)

postgres=# \d measurement 
        Partitioned table "public.measurement"
  Column   |  Type   | Collation | Nullable | Default 
-----------+---------+-----------+----------+---------
 city_id   | integer |           | not null | 
 logdate   | date    |           | not null | 
 peaktemp  | integer |           |          | 
 unitsales | integer |           |          | 
Partition key: RANGE (logdate)
Number of partitions: 0

It is not possible to insert data into this table as no partitions exist:

postgres=# INSERT INTO measurement VALUES(1, CURRENT_DATE, 25, 3);
ERROR:  no partition of relation "measurement" found for row
DETAIL:  Partition key of the failing row contains (logdate) = (2021-11-08).

Categories

Partitioning

See also

declarative partitioning, pg_partitioned_table