pg12 - partition by column that might have null values

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: pg12 - partition by column that might have null values
Date: 2019-10-02 06:17:37
Message-ID: CA+t6e1mnD+xP1a0kcuPMRDVUYhJu3rT9fGQ7sPe5=+d5sWykWQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2019-10-02 07:29:58 Re: pg12 - partition by column that might have null values
Previous Message Behrang Saeedzadeh 2019-10-01 13:42:33 Re: Slow PostgreSQL 10.6 query