Re: Support functions for GiST index on citext

Have you considered normalizing?

Here's a SQLFiddle example: http://sqlfiddle.com/#!15/61897/3/0. It uses
text instead of citext, but I imagine your results should be similar. Also,
I think usage of citext is generally recommended against.

The basic idea is to not use an array but use a second table instead. This
is well suited to your problem since you need a global unique constraint
across all entries; a unique index will be a very efficient way of
constraining that. You get your data back into the array form by doing a
JOIN and grouping by the first table's primary key.

On Fri, Aug 15, 2014 at 11:46 AM, Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com
> wrote:

> Hi -
>
> I have a table with a citext[] column, and I'm trying to write a
> uniqueness constraint for the array values. That is, two rows with
> {one,two} and {two,three} would conflict. Since it's citext, also
> {one,two} and {TWO, THREE} should conflict too.
>
> My first thought was to make a unique index using GIN, but that
> doesn't seem to be an option. Someone in IRC suggested an exclusion
> constraint, but it looks like the citext extension doesn't include any
> support for GiST operators.
>
> So now I'm trying to write my own GiSt-citext operator class to
> accomplish this. So far I have:
>
> CREATE OPERATOR CLASS _citext_ops DEFAULT
> FOR TYPE _citext USING gist AS
> OPERATOR 3 &&(anyarray, anyarray),
> OPERATOR 7 @>(anyarray, anyarray),
> OPERATOR 8 <@(anyarray, anyarray),
> OPERATOR 6 =(anyarray, anyarray),
> FUNCTION 7 citext_eq(citext, citext),
> STORAGE citext;
>
> I know I need more functions, but I'm not sure what they should be, or
> if its even possible to do this in raw SQL (I'm hosted on Heroku so I
> don't have the freedom to compile my own functions in C, even if I
> knew it).
>
> Can anyone guide me on how to finish this, or maybe on a simpler way
> to accomplish the same thing?
>
> Thanks!
> Chris
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>