NULL in column used for range partitioning in Postgres

Question

I have a table partitioned by range in Postgres 10.6. Is there a way to tell one of its partitions to accept NULL for the column used as partition key?

The reason I need this is: my table size is 200GB and it's actually not yet partitioned. I want to partition it going forward, so I thought I would create an initial partition including all of the current rows, and then at the start of each month I would create another partition for that month's data.

The issue is, currently this table doesn't have the column I'll use for partitioning, so I want to add the column (initially null) and then tell that initial partition to hold all rows that have null in the partitioning key.

Another option would be to not add the column as null but to set an initial date value, but that would be time and space consuming because of the size of that table.

Thanks @a_horse_with_no_name, I saw that one for list partitioning, but I didn't see anything that explicit for range partitions. — DWilches
– DWilches, Commented Apr 22, 2019 at 20:05

Laurenz Albe · Accepted Answer · 2019-04-22 21:05:29Z

2

I would upgrade to v11 and initially define the partitioned table with just a default partition that contains all the NULL values.

Then you can add other partitions and gradually move the data by updating the NULL values.

answered Apr 22, 2019 at 21:05

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

NULL in column used for range partitioning in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related