Support functions for GiST index on citext

Поиск
Список
Период
Сортировка
От Chris Hanks
Тема Support functions for GiST index on citext
Дата
Msg-id CAK7KUdBu8yi_GWEOh1Qvx4Lb9oTwPvxOMiWFuOtCzFi-SsTVCA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Support functions for GiST index on citext  (BladeOfLight16 <bladeoflight16@gmail.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "FarjadFarid\(ChkNet\)"
Дата:
Сообщение: Re: list of index
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Upgrading 9.0 to 9.3 - permission denied to pg_upgrade_utility.log