ProgrammingError: operator does not exist: bigint[] = integer[]

Question

I'm working with Python 3 and Postgres package (https://pypi.org/project/postgres/).

I have a table where one of the columns is an array of BIGINTs. When I try to work with this column (select, insert, etc) using a python list of numbers, I get an error.

The reason for the error seems to be that psycopg2 adapts the list to an array of INTEGERs rather than BIGINTs. It suggests explicit casting, but I couldn't find in the psycopg2 documentation how to do that. (The error goes away when I pass a list of numbers that are too big to fit in 4 bytes, the size of an INTEGER).

This is the code I ran:

db.all("SELECT phase from messages where recipients=%(reps)s", {'reps':[12,34]})

also happens with:

db.all("SELECT phase from messages where recipients=ARRAY[12, 34]")

And this is the error message I got:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/yoni/.local/lib/python3.6/site-packages/postgres/__init__.py", line 552, in all
    return cursor.all(sql, parameters)
  File "/home/yoni/.local/lib/python3.6/site-packages/postgres/cursors.py", line 145, in all
    self.execute(sql, parameters)
  File "/home/yoni/.local/lib/python3.6/site-packages/psycopg2/extras.py", line 313, in execute
    return super(NamedTupleCursor, self).execute(query, vars)
psycopg2.ProgrammingError: operator does not exist: bigint[] = integer[]
LINE 1: SELECT phase from messages where recepients=ARRAY[12,34]
                                                   ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

klin klin · Accepted Answer · 2019-01-22T08:53:00

Cast the array to bigint[]:

db.all("SELECT phase from messages where recipients=ARRAY[12, 34]::bigint[]")

ProgrammingError: operator does not exist: bigint[] = integer[]

1 Answers