Re: GIN indexed unique constraint?

On Sun, Jun 27, 2021 at 9:41 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Sun, Jun 27, 2021 at 11:18 AM Allan Kamau <kamauallan(at)gmail(dot)com>
> wrote:
> >> Is it possible to declare a UNIQUE constraint that uses GIN indexing?
>
> > Doesn't seem to be possible. The btree_gin extension would provide the
> > necessary code but it states explicitly that:
> > "... and they lack one major feature of the standard B-tree code: the
> > ability to enforce uniqueness."
>
> GIN stores all the component elements of its input values separately.
> It'd be tremendously hard even to identify which inputs share exactly
> the same component elements; let alone whether inputs sharing the
> same elements should be considered "equal". For example, ARRAY[1,2]
> and ARRAY[2,1] would give rise to identical sets of index entries in
> a GIN array_ops index.
>
> In short, no, this isn't something you do with a GIN index.
>
> regards, tom lane
>

Thank you David and Tom for your speedy and informative responses.

Allan.