On Sat, Aug 16, 2014 at 7:02 PM, Chris Hanks <christopher.m.hanks@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) >>> 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.) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
