Passing a list as parameter

[juntatalor]

• asyncpg version: 0.9.0
• PostgreSQL version: 9.6
• Python version: 3.5
• Platform: MacOS
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: -
• Can the issue be reproduced under both asyncio and
  uvloop?: Yes

I perform SQL query with where ... in clause and can not find a way to pass a list parameter:
results = await connection.fetch('SELECT * FROM post WHERE id in ($1)', [1, 2, 3])
I always get
TypeError: an integer is required (got type list)
Is it possible to pass a list parameter to query?

[elprans]

You need to explicitly cast your parameter to an array type like this:

results = await connection.fetch('SELECT * FROM post WHERE id in $1::int[]', [1, 2, 3])

[juntatalor]

Thanks for your advice, but it didn't work:

results = await connection.fetch('SELECT * FROM post WHERE id in $1::int[]', [1, 2, 3])
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$1"

After adding braces:
results = await connection.fetch('SELECT * FROM post WHERE id in ($1::int[])', [1, 2, 3])
asyncpg.exceptions.UndefinedFunctionError: operator does not exist: integer = integer[]

[elprans]

try

results = await connection.fetch('SELECT * FROM post WHERE id = any($1::int[])', [1, 2, 3])

[juntatalor]

That worked, thanks

[harpaj]

Hello,
we got stuck on this issue as well - would it be possible to allow the WHERE id in $1::int[] syntax or alternatively mention this somewhere in the documentation?
Thank you!

[elprans]

id IN <array> is not a valid SQL. I guess we could start an FAQ and put this there.

[samuelcolvin]

I've just spend quite a long time trying to find this. Would be very useful if it was added to the docs.

Part of the problem is that the "key words" in this problem "list", "in", "parameter" are pretty generic.

[logicbomb]

in postgres you can use the any function and pass in a list without having to type the parameter, e.g.

conn.fetch("select count(*) from foo where field = Any($1)", [["bar", "baz"]])