From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: pg12 - partition by column that might have null values
Date: Wed, 02 Oct 2019 09:29:58 +0200
Message-ID: <e506bb38aa4ac923709d075cb0e6e7710fe4c269.camel@cybertec.at> (raw)
In-Reply-To: <CA+t6e1mnD+xP1a0kcuPMRDVUYhJu3rT9fGQ7sPe5=+d5sWykWQ@mail.gmail.com>
References: <CA+t6e1mnD+xP1a0kcuPMRDVUYhJu3rT9fGQ7sPe5=+d5sWykWQ@mail.gmail.com>
Mariel Cherkassky wrote:
> 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..
Can "end_time" ever be modified?
If yes, it is a bad choice for a partitioning column.
If the NULL in "end_time" is meant to signify "no end", use the
value "infinity" instead of NULL. Then you can define the column
as NOT NULL.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, mariel.cherkassky@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: pg12 - partition by column that might have null values
In-Reply-To: <e506bb38aa4ac923709d075cb0e6e7710fe4c269.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox