Article Directory
PostgreSQL 11 supports creating a default (DEFAULT) partition for partition tables to store data that cannot match any other partitions. Obviously, only the RANGE partition table and the LIST partition table require the default partition.
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2018 PARTITION OF measurement
FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
The above example only creates a partition for 2018. If you insert data for 2017, the system will not be able to find the corresponding partition:
INSERT INTO measurement(city_id,logdate,peaktemp,unitsales) VALUES (1, '2017-10-01', 50, 200); ERROR: no partition of relation "measurement" found for row DETAIL: Partition key of the failing row contains (logdate) = (2017-10-01).
Using default partitions can solve this problem. Use the DEFAULT clause instead of the FOR VALUES clause when creating the default partition.
CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;
\d+ measurement
Table ""
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
peaktemp | integer | | | | plain | |
unitsales | integer | | | | plain | |
Partition key: RANGE (logdate)
Partitions: measurement_y2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
measurement_default DEFAULT
With the default partition, data without the partition will be inserted into the default partition:
INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
VALUES (1, '2017-10-01', 50, 200);
INSERT 0 1
select * from measurement_default;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2017-10-01 | 50 | 200
(1 row)
The default partition has the following restrictions:
A partition table can only have one DEFAULT partition;
For data already stored in the DEFAULT partition, the corresponding partition cannot be created; see the example below;
If an existing table is mounted as a DEFAULT partition, all data in the table will be checked; if the same data exists in the existing partition, an error will be generated;
The hash partition table does not support DEFAULT partitions, and it does not actually need to be supported.
Using default partitions can also cause some unforeseen problems. For example, insert a 2019 data into the measurement table, and since no corresponding partition is created, the record will also be assigned to the default partition:
INSERT INTO measurement(city_id,logdate,peaktemp,unitsales)
VALUES (1, '2019-03-25', 66, 100);
INSERT 0 1
select * from measurement_default;
city_id | logdate | peaktemp | unitsales
---------+------------+----------+-----------
1 | 2017-10-01 | 50 | 200
1 | 2019-03-25 | 66 | 100
(2 rows)
At this point, if you create another partition for 2019, the operation will fail. Because adding a new partition requires modifying the scope of the default partition (no longer contains data for 2019), but data for 2019 already exists in the default partition.
CREATE TABLE measurement_y2019 PARTITION OF measurement
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
ERROR: updated partition constraint for default partition "measurement_default" would be violated by some row
To solve this problem, you can first uninstall the default partition from the partition table (DETACH PARTITION), create a new partition, move the corresponding data in the default partition to the new partition, and finally remount the default partition.
ALTER TABLE measurement DETACH PARTITION measurement_default;
CREATE TABLE measurement_y2019 PARTITION OF measurement
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');
INSERT INTO measurement_y2019
SELECT * FROM measurement_default WHERE logdate >= '2019-01-01' AND logdate < '2020-01-01';
INSERT 0 1
DELETE FROM measurement_default WHERE logdate >= '2019-01-01' AND logdate < '2020-01-01';
DELETE 1
ALTER TABLE measurement ATTACH PARTITION measurement_default DEFAULT;
CREATE TABLE measurement_y2020 PARTITION OF measurement
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
\d+ measurement
Table ""
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
city_id | integer | | not null | | plain | |
logdate | date | | not null | | plain | |
peaktemp | integer | | | | plain | |
unitsales | integer | | | | plain | |
Partition key: RANGE (logdate)
Partitions: measurement_y2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
measurement_y2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
measurement_y2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
measurement_default DEFAULT
Official Documentation:Table Partitioning
Supplement: The automatic partition table function above postgresql10
1. List of columns
1. First create the main partition table:
create table fenbiao( id int, year varchar ) partition by list(year)
The data subtable is set here according to the year column; it is not visible after creation using navicat;
2. Create a subtable:
create table fenbiao_2017 partition of fenbiao for values in ('2017')
create table fenbiao_2018 partition of fenbiao for values in ('2018')
In this way, the data will be inserted into different subtables by rules in the past two days. If a data that does not conform to the rules is inserted, an error will be reported: no partition of relation "fenbiao" found for row.
2. Range table
1. Use the year column as the range to perform table classification
create table fenbiao2( id int, year varchar ) partition by range(year)
2. Create a sub-table
create table fenbiao2_2018_2020 partition of fenbiao2 for values from ('2018') to ('2020')
create table fenbiao2_2020_2030 partition of fenbiao2 for values from ('2020') to ('2030')
Note: Inserting year=2020 at this time will insert into the table below; if the following table range is 2021 to 2030, an error will be reported; inserting 2030 at the same time will also report an error; the range is equivalent to time a<=year<b;
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.