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..
