Обсуждение: Support functions for GiST index on citext
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
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
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 > >
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.)