Inserting into PG table from DuckDB table with same name does nothing in Python

Start by running the Python script and comparing its PostgreSQL logs with the DataGrip run, focusing on the same-name INSERT ... SELECT behavior. Trace the PostgreSQL extension's handling of the attached table and local table, then verify that the Python case writes both rows to pg_db.public.my_table without changing the differently named case.

Software versions

Postgres: 13.16
Python: 3.13.0
DuckDB: 1.2.0

Issue

I have the following SQL script attempting to insert into a Postgres table my_table all data from a local DuckDB table also called my_table:

INSTALL postgres;
LOAD postgres;

DETACH DATABASE IF EXISTS pg_db;
ATTACH '
    host=127.0.0.1
    port=5432
    dbname=postgres
    user=postgres
    password=postgres
' AS pg_db (TYPE POSTGRES);

CALL postgres_execute('pg_db', 'SET log_statement = ''all'';');

CREATE OR REPLACE TABLE pg_db.public.my_table (
    id INT,
    val TEXT
);

CREATE OR REPLACE TABLE my_table (
    id INT,
    val TEXT
);

INSERT INTO my_table VALUES
    (1, 'a'),
    (2, 'b');

INSERT INTO pg_db.public.my_table
    SELECT *
    FROM my_table;

SELECT *
FROM pg_db.public.my_table;

When executed from a query console in JetBrains DataGrip (which uses JDBC), this produces the expected output:

However, when executing the same commands from a Python script, no data is written into the PG table, with no errors or warnings thrown:

import duckdb

def main():
    db = duckdb.connect()
    db.execute(
        """
            INSTALL postgres;
            LOAD postgres;

            DETACH DATABASE IF EXISTS pg_db;
            ATTACH '
                host=127.0.0.1
                port=5432
                dbname=postgres
                user=postgres
                password=postgres
            ' AS pg_db (TYPE POSTGRES);

            CALL postgres_execute('pg_db', 'SET log_statement = ''all'';');

            CREATE OR REPLACE TABLE pg_db.public.my_table (
                id INT,
                val TEXT
            );

            CREATE OR REPLACE TABLE my_table (
                id INT,
                val TEXT
            );

            INSERT INTO my_table VALUES
                (1, 'a'),
                (2, 'b');

            INSERT INTO pg_db.public.my_table
                SELECT *
                FROM my_table;

            SELECT *
            FROM pg_db.public.my_table;
        """
    )

    print(db.fetchall())


if __name__ == "__main__":
    main()

# Output:
# []

When changing the name of the DuckDB table so that it is not the same as the Postgres table, the script now works:

import duckdb

def main():
    db = duckdb.connect()
    db.execute(
        """
            INSTALL postgres;
            LOAD postgres;

            DETACH DATABASE IF EXISTS pg_db;
            ATTACH '
                host=127.0.0.1
                port=5432
                dbname=postgres
                user=postgres
                password=postgres
            ' AS pg_db (TYPE POSTGRES);

            CALL postgres_execute('pg_db', 'SET log_statement = ''all'';');

            CREATE OR REPLACE TABLE pg_db.public.my_table (
                id INT,
                val TEXT
            );

            CREATE OR REPLACE TABLE my_table_local (
                id INT,
                val TEXT
            );

            INSERT INTO my_table_local VALUES
                (1, 'a'),
                (2, 'b');

            INSERT INTO pg_db.public.my_table
                SELECT *
                FROM my_table_local;

            SELECT *
            FROM pg_db.public.my_table;
        """
    )

    print(db.fetchall())


if __name__ == "__main__":
    main()

# Output:
# [(1, 'a'), (2, 'b')]

Logs

Inspecting the Postgres logs shows the following when the script is executed from DataGrip:

2025-03-07 18:32:59.492 UTC [850] LOG:  statement: COMMIT
2025-03-07 18:32:59.698 UTC [850] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

        SELECT oid, nspname
        FROM pg_namespace

        ORDER BY oid;

        SELECT pg_namespace.oid AS namespace_id, relname, relpages, attname,
            pg_type.typname type_name, atttypmod type_modifier, pg_attribute.attndims ndim,
            attnum, pg_attribute.attnotnull AS notnull, NULL constraint_id,
            NULL constraint_type, NULL constraint_key
        FROM pg_class
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
        JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
        JOIN pg_type ON atttypid=pg_type.oid
        WHERE attnum > 0 AND relkind IN ('r', 'v', 'm', 'f', 'p')
        UNION ALL
        SELECT pg_namespace.oid AS namespace_id, relname, NULL relpages, NULL attname, NULL type_name,
            NULL type_modifier, NULL ndim, NULL attnum, NULL AS notnull,
            pg_constraint.oid AS constraint_id, contype AS constraint_type,
            conkey AS constraint_key
        FROM pg_class
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
        JOIN pg_constraint ON (pg_class.oid=pg_constraint.conrelid)
        WHERE relkind IN ('r', 'v', 'm', 'f', 'p') AND contype IN ('p', 'u')
        ORDER BY namespace_id, relname, attnum, constraint_id;

        SELECT n.oid, enumtypid, typname, enumlabel
        FROM pg_enum e
        JOIN pg_type t ON e.enumtypid = t.oid
        JOIN pg_namespace AS n ON (typnamespace=n.oid)

        ORDER BY n.oid, enumtypid, enumsortorder;

        SELECT n.oid, t.typrelid AS id, t.typname as type, pg_attribute.attname, sub_type.typname
        FROM pg_type t
        JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
        JOIN pg_class ON pg_class.oid = t.typrelid
        JOIN pg_attribute ON attrelid=t.typrelid
        JOIN pg_type sub_type ON (pg_attribute.atttypid=sub_type.oid)
        WHERE pg_class.relkind = 'c'
        AND t.typtype='c'

        ORDER BY n.oid, t.oid, attrelid, attnum;

        SELECT pg_namespace.oid, tablename, indexname
        FROM pg_indexes
        JOIN pg_namespace ON (schemaname=nspname)

        ORDER BY pg_namespace.oid;

2025-03-07 18:32:59.707 UTC [850] LOG:  statement: COMMIT
2025-03-07 18:32:59.708 UTC [850] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        DROP Table  IF EXISTS "public"."my_table"
2025-03-07 18:32:59.709 UTC [850] LOG:  statement: CREATE TABLE "public"."my_table"("id" INTEGER, "val" VARCHAR);
2025-03-07 18:32:59.721 UTC [850] LOG:  statement: COMMIT
2025-03-07 18:32:59.785 UTC [850] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
2025-03-07 18:32:59.786 UTC [850] LOG:  statement: COPY "public"."my_table" FROM STDIN (FORMAT BINARY)
2025-03-07 18:32:59.788 UTC [850] LOG:  statement: COMMIT
2025-03-07 18:32:59.807 UTC [850] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
2025-03-07 18:32:59.808 UTC [850] LOG:  statement:
                COPY (SELECT "id", "val" FROM "public"."my_table" ) TO STDOUT (FORMAT "binary");

2025-03-07 18:32:59.808 UTC [850] LOG:  statement: COMMIT

And the following when run from Python:

2025-03-07 18:33:56.585 UTC [852] LOG:  statement: COMMIT
2025-03-07 18:33:56.586 UTC [852] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

        SELECT oid, nspname
        FROM pg_namespace

        ORDER BY oid;

        SELECT pg_namespace.oid AS namespace_id, relname, relpages, attname,
            pg_type.typname type_name, atttypmod type_modifier, pg_attribute.attndims ndim,
            attnum, pg_attribute.attnotnull AS notnull, NULL constraint_id,
            NULL constraint_type, NULL constraint_key
        FROM pg_class
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
        JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid
        JOIN pg_type ON atttypid=pg_type.oid
        WHERE attnum > 0 AND relkind IN ('r', 'v', 'm', 'f', 'p')
        UNION ALL
        SELECT pg_namespace.oid AS namespace_id, relname, NULL relpages, NULL attname, NULL type_name,
            NULL type_modifier, NULL ndim, NULL attnum, NULL AS notnull,
            pg_constraint.oid AS constraint_id, contype AS constraint_type,
            conkey AS constraint_key
        FROM pg_class
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
        JOIN pg_constraint ON (pg_class.oid=pg_constraint.conrelid)
        WHERE relkind IN ('r', 'v', 'm', 'f', 'p') AND contype IN ('p', 'u')
        ORDER BY namespace_id, relname, attnum, constraint_id;

        SELECT n.oid, enumtypid, typname, enumlabel
        FROM pg_enum e
        JOIN pg_type t ON e.enumtypid = t.oid
        JOIN pg_namespace AS n ON (typnamespace=n.oid)

        ORDER BY n.oid, enumtypid, enumsortorder;

        SELECT n.oid, t.typrelid AS id, t.typname as type, pg_attribute.attname, sub_type.typname
        FROM pg_type t
        JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
        JOIN pg_class ON pg_class.oid = t.typrelid
        JOIN pg_attribute ON attrelid=t.typrelid
        JOIN pg_type sub_type ON (pg_attribute.atttypid=sub_type.oid)
        WHERE pg_class.relkind = 'c'
        AND t.typtype='c'

        ORDER BY n.oid, t.oid, attrelid, attnum;

        SELECT pg_namespace.oid, tablename, indexname
        FROM pg_indexes
        JOIN pg_namespace ON (schemaname=nspname)

        ORDER BY pg_namespace.oid;

2025-03-07 18:33:56.598 UTC [852] LOG:  statement: DROP Table  IF EXISTS "public"."my_table"
2025-03-07 18:33:56.599 UTC [852] LOG:  statement: CREATE TABLE "public"."my_table"("id" INTEGER, "val" VARCHAR);
2025-03-07 18:33:56.616 UTC [852] LOG:  statement: COMMIT
2025-03-07 18:33:56.623 UTC [852] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
2025-03-07 18:33:56.624 UTC [852] LOG:  statement: COPY (SELECT "id", "val" FROM "public"."my_table" ) TO STDOUT (FORMAT "binary");
2025-03-07 18:33:56.624 UTC [852] LOG:  statement: COPY "public"."my_table" FROM STDIN (FORMAT BINARY)
2025-03-07 18:33:56.627 UTC [852] LOG:  statement: COMMIT
2025-03-07 18:33:56.628 UTC [852] LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ
2025-03-07 18:33:56.628 UTC [852] LOG:  statement: COPY (SELECT "id", "val" FROM "public"."my_table" ) TO STDOUT (FORMAT "binary");
2025-03-07 18:33:56.629 UTC [852] LOG:  statement: COMMIT