column reference X is ambiguous

Hello everyone 👋

Migrating from psycopg2 to asyncpg, I'm facing an issue with an executemany.

Here is my example:

Table structure:

CREATE TABLE table_name
(
    column1            text PRIMARY KEY,
    ...
    column8            JSON,
    column9            numeric,
    column10           numeric,
    ...
);

SQL request:

sql = """
UPDATE table_name
SET column8 = data.column8, column9 = data.column9, column10 = data.column10
FROM unnest ($1::table_name[]) AS data(column1, column8, column9, column10)
WHERE table_name.column1 = data.column1"""

args = [("id_1", "json_string_1", None, None), ("id_2", "json_string_2", decimal("5"), decimal("8")), ...]

loop.run_until_complete(conn.executemany(sql, args))

The result of this request is column reference "column8" is ambiguous

I don't know if it's related (and I know this library isn't the same as psycopg2) but before I had to pass a template for this request (like so: template = "(%s, %s::json, %s::numeric, %s::numeric)")

• asyncpg version: v0.23.0
• PostgreSQL version: 13.3
• Do you use a PostgreSQL SaaS? No
• Python version: 3.8.9 (Pyenv)
• Platform: Debian buster (Docker)
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: No
• Can the issue be reproduced under both asyncio and
  uvloop?: 🤷‍♂️

Start by reproducing the provided PostgreSQL UPDATE and asyncpg executemany example with the stated versions and arguments. Compare the database error with asyncpg's documented executemany behavior to determine whether a library change is requested; done means identifying a confirmed asyncpg defect or clarifying that the SQL must be changed by the user.