Re: Exclusion constraints on overlapping text arrays?

On 30/8/19 10:20 π.μ., Achilleas Mantzios wrote:
> 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

So you download latest version of smlar from here : http://sigaev.ru/git/gitweb.cgi?p=smlar.git
following is from commands given to FreeBSD but you get the point

% tar xvfz smlar-92dc9c7.tar.gz
cd smlar-92dc9c7
gmake (or make in linux)
if it complaints about not finding /contrib/contrib-global.mk then you do
setenv USE_PGXS 1 (export USE_PGXS=1 in linux)
and repeat the make step
sudo make install (again solve problems as above)

when installed successfully then :
psql
create extension smlar;
--and then create your exclude constraint :
alter table your_table_name ADD constraint constrname EXCLUDE USING gist (grant_number_codes _text_sml_ops with &&);

>>
>> 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

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