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*_
/messages/by-id/20141113183843.E8AC620362@smtp.hushmail.com

and pointed toward this 2013 discussion:

_*Todo item: Support amgettuple() in GIN*_
/messages/by-id/5297DC17.7000608@proxel.se

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

Subscribe to the mailing list <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

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

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*_
/messages/by-id/20141113183843.E8AC620362@smtp.hushmail.com

and pointed toward this 2013 discussion:

_*Todo item: Support amgettuple() in GIN*_
/messages/by-id/5297DC17.7000608@proxel.se

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

Subscribe to the mailing list <mailto:agency-general-request@lists.sourceforge.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

Ken Tanzer <ken.tanzer@gmail.com> writes:

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"

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 haven't heard of anyone working on it recently.

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?

As far as that goes, you'd just need to add parentheses.

regards, tom lane

On Fri, Aug 30, 2019 at 12:59 AM Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:

On 30/8/19 10:20 π.μ., Achilleas Mantzios wrote:

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

Well I set this up on a test machine running 11.4, and it certainly seems
to do what it's supposed to. Pretty cool, and thanks for pointing this out!

Is there much or any prospect of this becoming a supported extension?

And Tom--thanks for the pointer on the parentheses!

Cheers,
Ken

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

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