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