From: Ron <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Unique constraint across all partitions?
Date: Mon, 25 Jan 2021 18:09:32 -0600
Message-ID: <e91069b5-f6a8-c812-a4c9-9c5c4383cb4f@gmail.com> (raw)
In-Reply-To: <CAKFQuwZx-3A4Oqk69DTBk0MaVa9W7f=LPnmQpx1Q5Nj+=9PPVw@mail.gmail.com>
References: <e46db75f-02fc-1a79-5d04-92bc5ea05216@quadratum-braccas.com>
<3d00c1f4-646c-59b7-e4dc-1d51190d2139@gmail.com>
<CAKFQuwZx-3A4Oqk69DTBk0MaVa9W7f=LPnmQpx1Q5Nj+=9PPVw@mail.gmail.com>
On 1/25/21 6:01 PM, David G. Johnston wrote:
> On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
> On 1/25/21 4:44 PM, S Bob wrote:
>
> > Is it possible to enforce a unique constraint across all partitions for
> > either declarative or inheritance based partitioning? Preferably
> > declarative based.
>
> You can create unique indexes on parent tables. Or am I
> misunderstanding you?
>
>
> You seem to misunderstand how partitions and unique constraints work.
>
> Each partition gets its own copy of the unique index/constraint and
> ensures that duplicates do not appear therein. If the specification for
> the unique constraint is something like "id bigint" then the value 5 can
> appear on multiple partitions just fine - the partition key(s) effectively
> become part of the unique constraint/index even though the values are not
> actually stored in the index.
>
> So, yes, so long as your unique constraint takes into consideration the
> partitioning scheme it will enforce uniqueness across all of the
> partitions. But the answer to the full, but possibly implied, question is no.
This is why global indices are needed.
--
Angular momentum makes the world go 'round.
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-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Unique constraint across all partitions?
In-Reply-To: <e91069b5-f6a8-c812-a4c9-9c5c4383cb4f@gmail.com>
* 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