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.

Answer 1

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.