CODEWITHSUNDEEP
postgresql
user1409708
user1409708

Reputation: 1053

Range Partitions by Date with One Partition for NULL dates

I would like to have a table with deleted column containing the date the item was soft-deleted. Rows with NULL value in deleted column are the active ones. I was not able to figure our the syntax to create a partition for null values in deleted column. What is the syntax of creating such column?

create table my_table_pointing(street_id int, p_city_id int, name varchar(10), deleted date)
PARTITION BY RANGE (deleted);

CREATE TABLE my_table_pointing_2020 PARTITION OF my_table_pointing
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');


   CREATE TABLE my_table_pointing_active PARTITION OF my_table_pointing
     "for all rows where date is null"...

Thanks!

Upvotes: 1

Views: 1314

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

Provided you are on PG11 or later, you can create a default partition, and rows with deleted is null will be routed there.

create table my_table_pointing_active partition of my_table_pointing default;

Upvotes: 3

Related Questions