GIN indexed unique constraint?

On Sun, Jun 27, 2021 at 11:18 AM Allan Kamau <kamauallan@gmail.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."

https://www.postgresql.org/docs/current/btree-gin.html

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sun, Jun 27, 2021 at 11:18 AM Allan Kamau <kamauallan@gmail.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

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

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Sun, Jun 27, 2021 at 11:18 AM Allan Kamau <kamauallan@gmail.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.