Re: Exclusion constraints on overlapping text arrays?

On 30/8/19 3:42 π.μ., Ken Tanzer wrote:
> 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?
Maybe take a look at https://dba.stackexchange.com/questions/185174/error-data-type-text-has-no-default-operator-class-for-access-method-gist
>
> 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//
> /https://demo.agency-software.org/client/
> ken(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt