Psycopg3: passing a list as parameter for IN statement using named arguments

Question

How can I pass a list to an IN statement in a query using psycopg's named arguments?

Example:

cur.execute("""
        SELECT name
        FROM users
        WHERE id IN (%(ids)s)
        """,
        {"ids": [1, 2, 3]})

When I do that, I get the following error message:

psycopg.errors.UndefinedFunction: operator does not exist: integer = smallint[]

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

I think you need to modify the IN criteria - 1️⃣ don't use parenthesis to indicate a list - and the type of the dictionary's entry - use a tuple 2️⃣.

I don't have your table, but I did confirm your error with one of my tables, before fixing it (this is on [email protected])

cursor.execute("""
        SELECT *
        FROM users
        WHERE id IN %(ids)s 1️⃣
        """,
        dict(ids=(1, 2, 3)2️⃣)
)