arrays - 编程错误 : operator does not exist: bigint[] = integer[]

Question

我正在使用 Python 3 和 Postgres 包 ( https://pypi.org/project/postgres/ )。

我有一个表，其中一列是 BIGINT 数组。当我尝试使用 python 数字列表处理此列(选择、插入等)时，出现错误。

错误的原因似乎是 psycopg2 将列表调整为 INTEGER 数组而不是 BIGINT。它建议显式转换，但我无法在 psycopg2 文档中找到如何执行此操作。 (当我传递一个太大而不适合 4 个字节(整数的大小)的数字列表时，错误消失了)。

这是我运行的代码:

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

也发生在:

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

这是我收到的错误信息:

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.

Answer 1

将数组转换为 bigint[]:

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