From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ron <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Unique constraint across all partitions?
Date: Tue, 26 Jan 2021 09:33:11 +0100
Message-ID: <5ebaba7ee42d89f8bfb2599de203612024c86e8e.camel@cybertec.at> (raw)
In-Reply-To: <e91069b5-f6a8-c812-a4c9-9c5c4383cb4f@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>
<e91069b5-f6a8-c812-a4c9-9c5c4383cb4f@gmail.com>
On Mon, 2021-01-25 at 18:09 -0600, Ron wrote:
> > > > Is it possible to enforce a unique constraint across all partitions for
> > > > either declarative or inheritance based partitioning? Preferably
> > > > declarative based.
> >
> > 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.
Global indexes are likely more pain than gain.
They will make the best part of partitioning (attaching and detaching
partitions) painfully slow. Hardly worth it to gain a tiny little bit
of integrity. Within a partition, you can guarantee uniqueness easily.
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-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Unique constraint across all partitions?
In-Reply-To: <5ebaba7ee42d89f8bfb2599de203612024c86e8e.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