如何使用psycopg3实现PostgreSQL数组包含查询？

我的PostgreSQL 14数据库中有一张表，包含字符串数组类型字段tags TEXT[]。我在Python中有一个字符串列表["foo", "bar"]，想要查询表中包含该列表所有标签的行。

我期望执行的SQL语句是：SELECT * FROM mytable WHERE tags @> ARRAY['foo', 'bar']，但使用psycopg3模块无法正确生成这条语句。

当前代码如下：

sqlcmd = "SELECT * FROM mytable WHERE tags @> ARRAY[%(tags)s]"
params = {"tags": ["foo", "bar"]}

cursor = conn.cursor()
print(cursor.mogrify(sqlcmd, params))
#=> SELECT * FROM mytable WHERE tags @> ARRAY['{foo,bar}']

当只传入单个标签时，生成的语句为… tags @> ARRAY['{foo}']。

解决方法 #

psycopg3会自动把Python列表转换为PostgreSQL的数组类型，不需要手动用ARRAY[]包裹参数——否则会导致嵌套数组问题（生成的ARRAY['{foo,bar}']是把数组格式的字符串当成了单个元素）。

正确的写法直接将参数作为数组使用：

sqlcmd = "SELECT * FROM mytable WHERE tags @> %(tags)s"
params = {"tags": ["foo", "bar"]}

cursor = conn.cursor()
print(cursor.mogrify(sqlcmd, params))
#=> SELECT * FROM mytable WHERE tags @> ARRAY['foo','bar']

这里的@>操作符是PostgreSQL中判断数组包含关系的正确用法，确保返回的行包含你传入的所有标签。

内容的提问来源于stack exchange，提问作者Phrogz