My PostgreSQL 14 database has a table with a field that is an array of strings, tags TEXT[]. I have a list of strings in Python ["foo", "bar"], and I want to select rows in the table that have all the tags in the list.

Based on this article I believe the SQL i want to execute is: SELECT * FROM mytable WHERE tags @> ARRAY['foo', 'bar'].

I cannot figure out how to do this with the psycopg3 module.

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}']

If I supply just one tag in the list I get … tags @> ARRAY['{foo}'].

My PostgreSQL 14 database has a table with a field that is an array of strings, tags TEXT[]. I have a list of strings in Python ["foo", "bar"], and I want to select rows in the table that have all the tags in the list.

Based on this article I believe the SQL i want to execute is: SELECT * FROM mytable WHERE tags @> ARRAY['foo', 'bar'].

I cannot figure out how to do this with the psycopg3 module.

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}']

If I supply just one tag in the list I get … tags @> ARRAY['{foo}'].

• python
• postgresql
• psycopg2
• psycopg3

1 Answer 1

A one-dimensional array of strings in PostgreSQL can be represented as:

• ARRAY['foo', 'bar']
• '{"foo", "bar"}'
• '{foo, bar}'

In the last, the strings only have to be double-quoted if they contain certain characters, like a space or a comma. The following is a valid one-dimensional array containing two strings:

• '{"with spaces", nospaces}'

The psycopg3 library does the right thing here, with no need for the ARRAY[…] wrapper:

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

cursor = conn.cursor()
print(cursor.mogrify(sqlcmd, params))
#=> SELECT * FROM mytable WHERE tags @> '{foo,big-bar,"jim jam","baz,bat"}'