From: | BladeOfLight16 <bladeoflight16(at)gmail(dot)com> |
---|---|
To: | Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Support functions for GiST index on citext |
Date: | 2014-08-16 22:27:14 |
Message-ID: | CA+=1U=UFwGgb_3bh6o7ryOcrr-re8iHewnBLz1uOiTWhOpLWiQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Hanks | 2014-08-16 23:02:50 | Re: Support functions for GiST index on citext |
Previous Message | Adrian Klaver | 2014-08-16 21:19:35 | Re: logfile character encoding |