2

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.

1 Answer 1

3

Cast the array to bigint[]:

db.all("SELECT phase from messages where recipients=ARRAY[12, 34]::bigint[]")
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.