Re: Support functions for GiST index on citext

On Sat, Aug 16, 2014 at 7:02 PM, Chris Hanks
<christopher.m.hanks@gmail.com> wrote:
> Thanks, I have considered it, and I'd like to stick with an array for
> my use case if possible. Also, if citext is being advised against, I'd
> like to know about it, since I use it extensively and have never had
> an issue with it. Can anyone shed some light on this?
>
> Thanks!
> Chris
>
> On Sat, Aug 16, 2014 at 6:27 PM, BladeOfLight16
> <bladeoflight16@gmail.com> wrote:
>> 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.m.hanks@gmail.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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>

I don't mean to spam the list, but just a final check to see whether
anyone has a solution to this? For my use case, I think that if I
can't get an exclusion constraint working for this I'll probably wind
up just using a GIN index and not worrying about the occasional
duplicate. Though I'm not sure if that's possible either right now.

Thanks!

(Also, my apologies for top-posting earlier - I don't use mailing
lists often and it's easy to forget.)