column reference X is ambiguous

[RichardFevrier]

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?: 🤷‍♂️

[elprans]

The provided snippet works just fine for me:

import asyncio
import asyncpg
import enum
from decimal import Decimal


async def main():
    conn = await asyncpg.connect()
    try:
        await conn.execute('''
            CREATE TABLE table_name
            (
                column1            text PRIMARY KEY,
                column8            JSON,
                column9            numeric,
                column10           numeric
            );
        ''')

        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"))]

    finally:
        await conn.execute('DROP TABLE IF EXISTS table_name')
        await conn.close()

asyncio.run(main())

Can you show a query that actually fails?