Psycopg2 "operator does not exist: integer[] = integer" error

Question

Given a table called namespace_list with an INTEGER[] column called namespace_ids.

I have the following query:

query = SELECT * FROM namespace_list WHERE namespace_ids = ANY(%s) 
and application_id=%s

which I am executing as:

cur.execute(query, data)

and where data is:

data = ([1, 2], 1)

I am getting the following error:

operator does not exist: integer[] = integer at character 50\nHINT:
No operator matches the given name and argument type(s).
You might need to add explicit type casts.

Why is this not working? Looking at http://www.postgresql.org/message-id/CAG7mmoxZdRdatjWSYLBCVGJ4CPMa38ARSZByWe9xrWHKP0mB1g@mail.gmail.com, and other tutorials on Postgres Arrays, it seems that I have the correct query.

I am following what http://initd.org/psycopg/docs/usage.html#adapt-list has as an example as well. Is there anything wrong with my query, or the way I am using arrays with psycopg2?

fog · Accepted Answer · 2014-02-19 08:20:48Z

2

The problem is that the SQL expression:

<column> = ANY(<array>)

returns true if the scalar value in <column> is equal to any value in <array> comparing the values one by one. But your column is not a scalar value, it is an array and that's why PostgreSQL says:

operator does not exist: integer[] = integer

An operator to compare an array (left) to any integer (right) doesn't exists. To fix this you can use an intersection operator (&&) (if you need to match just one id from both sets) or the equality operator (=) if you want to match all array elements:

SELECT * FROM namespace_list WHERE namespace_ids && %s and application_id=%s

The trick here is that psycopg converts Python lists to literal arrays (ARRAY[...]) that you can use anywhere you would use them in standard SQL.

edited Feb 19, 2014 at 8:20

answered Feb 18, 2014 at 18:24

fog

3,4411 gold badge27 silver badges32 bronze badges

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

4 Comments

darksky Over a year ago

How come? Psycopg2 says we can do this: ids = [10, 20, 30] cur.execute("SELECT * FROM data WHERE id = ANY(%s);", (ids,)). This is exactly what I'm doing.

darksky Over a year ago

I need to check for exact match. When doing query SELECT * FROM namespace_list WHERE namespace_ids = ARRAY%s and application_id=%s with data ([1, 2], 1), I get: syntax error at or near "," at character 64. Why is it not accepting the array now?

fog Over a year ago

Because it is not what you are doing. where id = ANY(%s) works because on the left you have a scalar and not an array. But you said that namespace_ids is ab array so (see my answer) it can't work.

fog Over a year ago

If you want equality between arrays just use =. Remove ARRAY from the query in your comment and it should work.

Collectives™ on Stack Overflow

Psycopg2 "operator does not exist: integer[] = integer" error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related