Re: array exclusion constraints

CREATE TABLE test (
foo text[],
EXCLUDE USING gist (foo WITH &&)
);

ERROR: data type text[] 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.

It looks like exclusion constraints don't work with arrays, there's no gist opclass for them. This would be a nice to have feature and, if I recall correctly, exclusion constraints were meant to work both with ranges and arrays. Am I missing something?

Regards,
Francis

francisk(at)hush(dot)com wrote:
>
> CREATE TABLE test (
> foo text[],
> EXCLUDE USING gist (foo WITH &&)
> );
>
> ERROR: data type text[] 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.
>
> It looks like exclusion constraints don't work with arrays, there's no gist opclass for them. This would be a nice to have feature and, if I recall correctly, exclusion constraints were meant to work both with ranges and arrays. Am I missing something?

I recall I spent some time thinking about this issue, and even could find the
link (also referenced in "Indexes" section of
https://wiki.postgresql.org/wiki/TODO)

http://www.postgresql.org/message-id/CA+TgmobZhfRJNyz-fyw5kDtRurK0HjWP0vtP5fGZLE6eVSWCQw@mail.gmail.com

Regarding implementation, more recent thread summarizes the difficulties:

http://www.postgresql.org/message-id/5297DC17.7000608@proxel.se

--
Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at