Cannot use int as param in SELECT clause

[SirRhynus]

• asyncpg version: 0.27.0
• PostgreSQL version: "PostgreSQL 14.6 (Ubuntu 14.6-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit"
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: It's a local install
• Python version: 3.10.8
• Platform: Pop!_OS Linux
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: no, with conda
• If you built asyncpg locally, which version of Cython did you use?: /
• Can the issue be reproduced under both asyncio and uvloop?: /

I want to for example execute the query SELECT 1 AS id; but with a param. So I used this function to test it out:

async def test():
    conn = await asyncpg.connect(db_uri)
    row = await conn.fetchrow('SELECT $1 AS id;', 1)
    print(row)
    await conn.close()

asyncio.run(test())

This results in the error: asyncpg.exceptions.DataError: invalid input for query argument $1: 1 (expected str, got int), even though it should be a perfectly good SQL query if it was an int.

[aydabd]

The error you encountered is due to the fact that the asyncpg library expects query arguments to be passed as strings. In your code, you're passing an integer (1) as the argument, which is causing the error. This is the correct one: row = await conn.fetchrow('SELECT $1 AS id;', str(1))

[elprans]

asyncpg does not expect arguments to be passed as strings, it expects them to correspond to the type of the argument inferred by Postgres. Type correspondence is documented here.

The issue here is that there is no context for Postgres to infer $1 as an integer, so it defaults to the default assumption that the argument is text. It is generally a good idea to avoid relying on argument type inference and instead always specify the argument type explicitly via a cast, like so:

select $1::int as id;