Exclusion constraints on overlapping text arrays?

From: Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Exclusion constraints on overlapping text arrays?
Date: 2019-08-30 00:42:24
Message-ID: CAD3a31WW3qFAhe19OEvgDOegr_cJZVAQ4bVpSZ83bZCmWe1=mw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. Using 9.6.14, I was setting up a table with this:

EXCLUDE using gist (EXCLUDE using gist (grant_number_codes with &&)

Where grant_numbers is a varchar[]. I get this error:

ERROR: data type character varying[] has no default operator class for
access method "gist"
HINT: You must specify an operator class for the index or define a default
operator class for the data type.

I did some Googling, and it looks like you could do this for integer arrays
with the intarray extension. I didn't see anything I'd recognize as an
equivalent for varchar (or text) arrays. Is there any way to do this now?
And if not, is there much prospect of this being implemented at some point?

I found a couple of old threads about this. Not sure if they are still
relevant, but listed below.

Also, on a side note, I tried using grant_number_codes::text[] with &&, but
that got a syntax error. Does that mean casting isn't allowed at all in
these constraints?

Thanks in advance,
Ken

1) This 2014 thread asked about this:

*array exclusion constraints*
https://www.postgresql.org/message-id/flat/20141113183843.E8AC620362%40smtp.hushmail.com

and pointed toward this 2013 discussion:

*Todo item: Support amgettuple() in GIN*
https://www.postgresql.org/message-id/flat/5297DC17.7000608%40proxel.se

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Luca Ferrari 2019-08-30 06:29:41 Re: literal vs dynamic partition constraint in plan execution
Previous Message Adrian Klaver 2019-08-29 22:29:51 Re: How to log 'user time' in postgres logs