3

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.
mjuopperi
  • 773
  • 7
  • 25
Leonardo Freua
  • 123
  • 1
  • 1
  • 7
  • 2
    `... WHERE id = ANY(%(ids)s)""", {"ids": [1, 2, 3]}` per [List adaption](https://www.psycopg.org/docs/usage.html#adapt-list). – Adrian Klaver Dec 20 '22 at 19:32

1 Answers1

0

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 psycopg2@2.8.6)

cursor.execute("""
        SELECT *
        FROM users
        WHERE id IN %(ids)s 1️⃣
        """,
        dict(ids=(1, 2, 3)2️⃣)
)
JL Peyret
  • 10,917
  • 2
  • 54
  • 73
  • 3
    This is how to do it in psycopg2 but the question specifically says psycopg3. Here is how to do it in psycopg3, as per note under "Lists adaptation" of psycopg3 docs (https://www.psycopg.org/psycopg3/docs/basic/adapt.html#lists-adaptation): `cur.execute("SELECT name FROM users WHERE id = ANY(%(ids)s)", {"ids": [1, 2, 3]})`. Psycopg3 handles serialises lists / tuples as Postgres arrays which is why this change is needed. I also don't think this should be marked as a duplicate of the psycopg2 question as this specifically says psycopg3, which has a different answer. – drewsberry Jan 18 '23 at 10:10
  • @drewsberry Could you put your answer as a proper answer, so I can upvote it, and downvote this answer? – morten Aug 16 '23 at 13:09
  • @morten oh, do feel free to DV as you see fit Right now in fact. And the Q was originally tagged as `psycopg2` and `psycopg3` when I answered it, as a check of edit history will show. – JL Peyret Aug 16 '23 at 16:16