postgres_scanner
postgres_scanner copied to clipboard
Published
20 hours ago •
duckdb
duckdb
Inserting into PG table from DuckDB table with same name does nothing in Python
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:
| id | val |
|---|---|
| 1 | a |
| 2 | b |
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
