From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Exclusion constraints on overlapping text arrays? |
Date: | 2019-08-30 07:59:05 |
Message-ID: | a3abc42a-0125-be8d-74ec-1c7a60b403b7@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Luca Ferrari | 2019-08-30 08:02:35 | Re: literal vs dynamic partition constraint in plan execution |
Previous Message | Achilleas Mantzios | 2019-08-30 07:20:10 | Re: Exclusion constraints on overlapping text arrays? |