Postgres 11 - Range Partitioning on a Date Column with NULL values

Question

We have a table structure with parent and child records inside the same table. They are referenced via a column (parentId) which references the parent (and null on the top parent records). Also, we have a requested date column we do all of our querying by but the only records which have a value for this are the parent records.

Since all of our queries are filtering on the parent results initially and use this date, we want to paritition the table on this date range but understand this isn't possible due to there being null values on the records for the child records.

Aside from creating a "dummy" value (some arbitrary date that would never be caught in our filter) on each and every child record in the database, what other option do we have? Is it not possible to somehow create a separate partition for all of the null values that we never query for anyway?

Answer 1

PostgreSQL 11 allows you create a default partition for range-partitioned tables, which will receive the NULL values.

You can add a check constraint demanding the column be NULL, if you want to prevent other rows not covered by existing partitions from going into that default table.

create table for_nulls PARTITION OF parent_table (check (date_requested is null)) DEFAULT;