psycopg2: WHERE =ANY with multiple columns

PostgreSQL v14 Psycopg2 v2.9.3

I can do this in straight PostgreSQL / pgsql, however I can't seem to get it in Psycopg2.

Given an example table:

    CREATE TABLE accounts (
       id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       account_name TEXT,
       account_number TEXT NOT NULL,
       other_account_info TEXT
    )

    -- Enforces uniqueness for the pair, both non-null
    CREATE UNIQUE INDEX accounts_name_number_unique_idx ON accounts (account_name, account_number);

    -- Enforces uniqueness for the account_number for a non-null account_name
    CREATE UNIQUE INDEX accounts_number_unique_idx ON accounts (account_number) WHERE account_name IS NULL;

I want to match on an accounts record using 2 fields by doing something like the following:

SELECT *
FROM accounts
WHERE (account_name, account_number) =ANY(VALUES('name', number'), ('name1', 'number2'))

This of course works just fine when I run it directly in psql, however I can't get psycopg2 to format the SQL properly.

NOTE: I don't want to simply do WHERE account_name = %(account_name)s AND account_number = %(account_number)s because I will have an unknown number of account_name / account_number pairs to query on and don't want to dynamically generate the SQL.

I've tried the following:

template: str = f"""
SELECT *
FROM accounts
WHERE (account_name, account_number) = ANY(VALUES%(account_list)s)
"""

inserts: dict = {'account_list': ('account_name1', 'account_number1')}
execute(cur=_cursor, sql=template, argslist=inserts)

And that works! But, as soon as I add a second account_name and account_number to the parameters and make it a tuple of tuples it breaks with: UndefinedFunction: operator does not exist: text = record

template: str = f"""
SELECT *
FROM accounts
WHERE (account_name, account_number) = ANY(VALUES%(account_list)s)
"""

inserts: dict = { 'account_list': (('account_name1', 'account_number1',),('account_name2', 'account_number2',)) }
execute(cur=_cursor, sql=template, argslist=inserts)

I've also tried make the parameter a list and removing "VALUES" from the SQL template, but it breaks and I get DatatypeMismatch: cannot compare dissimilar column types text and unknown at record column 1

template: str = f"""
SELECT *
FROM accounts
WHERE (account_name, account_number) = ANY(%(account_list)s)
"""

inserts: dict = { 'account_list': [('account_name1', 'account_number1'),('account_name2', 'account_number2')] }
execute(cur=_cursor, sql=template, argslist=inserts)

I recognize that in some situations, I need to cast the individual parameters, but I can't figure out how to do that in psycopg2.

Any help would be really appreciated!

Solution

Rather than using any() it may be easier to use unnest() to create a temporary table of your account_name and account_number pairs of interest and join this to your accounts table.

sql = "select * from accounts
       join unnest(%(names)s, %(numbers)s) as query(name, number) 
       on query.name = accounts.account_name and 
          query.number = accounts.account_number;"
accounts = (('account_name1', 'account_number1',),
            ('account_name2', 'account_number2',))
names, numbers = map(list, zip(*accounts))
cur.execute(sql, {"names": names, "numbers": numbers})

I have not paid any attention to handling of null values of accout_name in this example as I was unclear how you were planning on handling equality in this situation and you may need to adjust the code accordingly.