Hey,
In PG12 I'm trying to create partitions by range on a date column that might be null (indicates it is the most recent version of the object). My PK has to include the partition column, therefore I'm getting an error that I cant create a primary key with the specific column because it has null values.
For example :
\d object_revision
Table "public.object_revision"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
id | integer | | not null | nextval('mariel_dates_test_id_seq'::regclass)
end_time | timestamp without time zone | | |
object_hash | text | | |
Indexes:
"id_pk" PRIMARY KEY, btree (id)
Lets say that the same object (object_hash) can have many revisions, the end_time is the time it was last updated. I'm trying to create this table as a range partition on the end_time. However, when I try to add the pk I'm getting an error :
ALTER TABLE object_revision ADD CONSTRAINT object_revision_id_end_time PRIMARY KEY (id,end_time);
ERROR: column "end_time" contains null values
does someone familiar with a workaround ? I know that in postgresql as part of the primary key definition unique and not null constraints are enforced on each column and not on both of them. However, this might be problematic with pg12 partitions..