Parameterizing "select * from values($1)" subquery of a main insert query throws DatatypeMismatchError

[papadp]

• asyncpg version: 0.26.0
• PostgreSQL version: 11.18
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: not using
• Python version: 3.9.10
• Platform: Debian GNU/Linux 11 (bullseye)
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: didnt build ocally
• Can the issue be reproduced under both asyncio and uvloop?: didnt check - I'm using asyncio

I'm trying to run the following query as an insert-if

    await conn.execute('''
        INSERT INTO test2 (a)

        SELECT * FROM (
          VALUES ($1)
        )

        AS temp (a)

        WHERE (
          SELECT count(*)
          FROM test
          WHERE t != $2
        ) = 0''', 1093, -863)

I'm not able to parameterize the VALUES($1) part of the query, when swapping to a hardcoded const my query works well, otherwise I'm getting this error

Traceback (most recent call last):
  File "/data/./src/utils/testing/test_conditional_insert_pg.py", line 43, in <module>
    asyncio.get_event_loop().run_until_complete(main())
  File "/usr/local/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/data/./src/utils/testing/test_conditional_insert_pg.py", line 8, in main
    await conn.execute('''
  File "/usr/local/lib/python3.9/site-packages/asyncpg/connection.py", line 320, in execute
    _, status, _ = await self._execute(
  File "/usr/local/lib/python3.9/site-packages/asyncpg/connection.py", line 1659, in _execute
    result, _ = await self.__execute(
  File "/usr/local/lib/python3.9/site-packages/asyncpg/connection.py", line 1684, in __execute
    return await self._do_execute(
  File "/usr/local/lib/python3.9/site-packages/asyncpg/connection.py", line 1711, in _do_execute
    stmt = await self._get_statement(
  File "/usr/local/lib/python3.9/site-packages/asyncpg/connection.py", line 398, in _get_statement
    statement = await self._protocol.prepare(
  File "asyncpg/protocol/protocol.pyx", line 168, in prepare
asyncpg.exceptions.DatatypeMismatchError: column "a" is of type integer but expression is of type text
HINT:  You will need to rewrite or cast the expression.

Running the query in the following way doesn't produce an error:

    await conn.execute('''
        INSERT INTO test2 (a)

        SELECT * FROM (
          VALUES (2)
        )

        AS temp (a)

        WHERE (
          SELECT count(*)
          FROM test
          WHERE t != $1
        ) = 0''', 1093)

[elprans]

Add an explicit type cast to your arguments. It is usually a bad idea to rely on reverse type inference of arguments:

await conn.execute('''
        INSERT INTO test2 (a)

        SELECT * FROM (
          VALUES ($1::int)
        )

        AS temp (a)

        WHERE (
          SELECT count(*)
          FROM test
          WHERE t != $2::int
        ) = 0''', 1093, -863)