Managing Partitions - Range¶
Let us understand how to manage partitions for the table users_range_part
.
All users data created in a specific year should go to the respective partition created.
For example, all users data created in the year of 2016 should go to
users_range_part_2016
.We can add partition to existing partitioned table using
CREATE TABLE partition_name PARTITION OF table_name
.We can have a partition for default values so that all the data that does not satisfy the partition condition can be added to it.
We can have a partition for specific range of values using
FOR VALUES FROM (from_value) TO (to_value)
as part ofCREATE TABLE partition_name PARTITION OF table_name
.Once partitions are added, we can insert data into the partitioned table.
Note
Here is how we can create partition for default values for a range partitioned table users_range_part.
Error
As there is a overlap between the previous partition and below one, command to create partition for data ranging from 2016-01-01 till 2017-12-31 will fail.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
(psycopg2.errors.InvalidObjectDefinition) partition "users_range_part_2017" would overlap partition "users_range_part_2016"
[SQL: CREATE TABLE users_range_part_2017 PARTITION OF users_range_part
FOR VALUES FROM ('2016-01-01') TO ('2017-12-31')]
(Background on this error at: http://sqlalche.me/e/13/f405)
Note
This is how we can create partitions for the years 2017, 2018, 2019 etc