Re: Unique constraint across all partitions?

On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr(at)gmail(dot)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.

David J.