asyncpg.exceptions.DataError: invalid input for query argument $1

[pbryan]

I'm seeing that asyncpg doesn't always infer the type of a parameter, even when it's unambigous.

Example:

import asyncio
import asyncpg

async def main():
    conn = await asyncpg.connect("postgresql://user@localhost/database")
    async with conn.transaction():
        async for row in conn.cursor("SELECT $1;", True):
            print(row)

asyncio.run(main())

Output:

Traceback (most recent call last):
  File "asyncpg/protocol/prepared_stmt.pyx", line 168, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
  File "asyncpg/protocol/codecs/base.pyx", line 206, in asyncpg.protocol.protocol.Codec.encode
  File "asyncpg/protocol/codecs/base.pyx", line 111, in asyncpg.protocol.protocol.Codec.encode_scalar
  File "asyncpg/pgproto/./codecs/text.pyx", line 29, in asyncpg.pgproto.pgproto.text_encode
  File "asyncpg/pgproto/./codecs/text.pyx", line 12, in asyncpg.pgproto.pgproto.as_pg_string_and_size
TypeError: expected str, got bool

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/pbryan/Projects/fondat-postgresql/tests/test.py", line 10, in <module>
    asyncio.run(main())
  File "/usr/lib/python3.10/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/lib/python3.10/asyncio/base_events.py", line 646, in run_until_complete
    return future.result()
  File "/home/pbryan/Projects/fondat-postgresql/tests/test.py", line 7, in main
    async for row in conn.cursor("SELECT $1;", True):
  File "/home/pbryan/Projects/fondat-postgresql/.venv/lib/python3.10/site-packages/asyncpg/cursor.py", line 223, in __anext__
    buffer = await self._bind_exec(self._prefetch, self._timeout)
  File "/home/pbryan/Projects/fondat-postgresql/.venv/lib/python3.10/site-packages/asyncpg/cursor.py", line 129, in _bind_exec
    buffer, _, self._exhausted = await protocol.bind_execute(
  File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute
  File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions.DataError: invalid input for query argument $1: True (expected str, got bool)

A boolean True value seems pretty unambiguous a parameter, so I'm at a loss as to why It can't be inferred. I see I can sidestep this with the following statement:

async for row in conn.cursor("SELECT $1::bool;", True):

Questions:

1. Is this expected behavior?
2. Is the correct workaround/solution to always type-hint parameters to avoid such problems?

asyncpg: 0.26

[elprans]

This is expected behavior (of Postgres): untyped arguments are assumed to be strings ("unknown" to be exact, which is the same). The correct approach is to always indicate the argument type via a cast.

[pbryan]

OK, thanks again.