Inserting more than one row into a table with a BYTEA column fails

[matsimko]

• asyncpg version: 0.27.0
• PostgreSQL version: 14.8
• Python version: 3.11.4
• Did you install asyncpg with pip?: yes

---

I have a table called attachment which contains a BYTEA column called data.

Inserting a single row succeeds:

    await conn.execute(
        """
        INSERT INTO attachment(filename, data, notification_id)
        VALUES($1::VARCHAR, $2, $3::INTEGER) RETURNING attachment.id
        """,
        'attachment.txt', b'test attachment\n', 14
    )

Inserting more than one row fails:

    await conn.execute(
        """
        INSERT INTO attachment (filename, data, notification_id)
        SELECT p0::VARCHAR, p1::BYTEA, p2::INTEGER
        FROM (VALUES ($1::VARCHAR, $2, $3::INTEGER, 0), ($4::VARCHAR, $5, $6::INTEGER, 1))
        AS imp_sen(p0, p1, p2, sen_counter)
        """,
        'attachment.txt', b'test attachment\n', 14, 'attachment2.txt', b'test attachment2\n', 14
    )

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 bytes

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

Traceback (most recent call last):
  File "/home/user/project/app/tmp.py", line 30, in <module>
    loop.run_until_complete(run())
  File "/usr/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/home/user/project/app/tmp.py", line 9, in run
    await conn.execute(
  File "/home/user/.local/share/virtualenvs/project-_FJSOHGC/lib/python3.11/site-packages/asyncpg/connection.py", line 319, in execute
    _, status, _ = await self._execute(
                   ^^^^^^^^^^^^^^^^^^^^
  File "/home/user/.local/share/virtualenvs/project-_FJSOHGC/lib/python3.11/site-packages/asyncpg/connection.py", line 1658, in _execute
    result, _ = await self.__execute(
                ^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/.local/share/virtualenvs/project-_FJSOHGC/lib/python3.11/site-packages/asyncpg/connection.py", line 1683, in __execute
    return await self._do_execute(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/.local/share/virtualenvs/project-_FJSOHGC/lib/python3.11/site-packages/asyncpg/connection.py", line 1730, in _do_execute
    result = await executor(stmt, None)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  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 $2: b'test attachment\n' (expected str, got bytes)

The problem can be avoided by explicitly casting to BYTEA:

FROM (VALUES ($1::VARCHAR, $2::BYTEA, $3::INTEGER, 0), ($4::VARCHAR, $5::BYTEA, $6::INTEGER, 1))

However, I cannot do that because I am using SQLAlchemy, which generates these statements.

[elprans]

This would be an SQLAlchemy issue. asyncpg relies on PostgreSQL argument introspection, so if Postgres determines it's a text argument, then it must be a text argument. Explicitly casting arguments is a good practice in any case.

[matsimko]

Yes, turns out SQLAlchemy fixed this in version 2.0.13 by adding casts.