operator does not exist: integer[] @@ integer[]

Question

I have added an intarray column to my table and successfully added data to the column.

CREATE TABLE my_table
(
  ...
  tag_ids integer[],
)

When I try a query like this:

SELECT id, tag_ids from my_table where tag_ids @@ ARRAY[1,2]

I get this error:

ERROR:  operator does not exist: integer[] @@ integer[]
LINE 1: SELECT id, tag_ids from core_tile where tag_ids @@ ARRAY[1,2...
                                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I installed 9.3 via Homebrew for OS X, which I believe includes the intarray module, and like I said I added the array column and data no problem. Also this:

SELECT * FROM pg_available_extensions
...
"intarray";"1.0";"";"functions, operators, and index support for 1-D arrays of integers"

Update:

I wondered if I needed a specific index before the operators would work. In the example in the docs they add an index like this:

CREATE INDEX my_index ON my_table USING GIST (tag_ids gist__int_ops);

But when I run that I get:

ERROR:  operator class "gist__int_ops" does not exist for access method "gist"

Am I missing an extension or is something wrong with the query or what?

Anentropic · Accepted Answer · 2014-02-05 12:36:55Z

3

Well, I found the operators here such as @> all work:
http://www.postgresql.org/docs/9.3/static/functions-array.html

It's just the additional ones here that didn't:
http://www.postgresql.org/docs/9.3/static/intarray.html

That suggested to me the solution:

CREATE EXTENSION intarray;

Running this against my db got all the operators working... I had assumed since Postgres was installed with intarray support it was all ready to go. But it has to be manually enabled for the db.

I guess this is so obvious for the Postgres devs they forgot to put it in the documentation.

edited Feb 5, 2014 at 12:36

answered Feb 3, 2014 at 19:06

Anentropic

34.2k13 gold badges111 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erwin Brandstetter Over a year ago

There are a couple of related (possible helpful) answers suggesting to create the extension intarray for additional operators.

Chief Over a year ago

holy crap, thank you so much I was sitting here scratching my head why intarray stuff wouldn't work.

Collectives™ on Stack Overflow

operator does not exist: integer[] @@ integer[]

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related