duckdb / duckdb/duckdb-postgres
Silent switching from binary to text copy
- Dominant language
- C++
- Stars
- 372
- Forks
- 105
- Avg merge
- 10h 19m
- Merged PRs (30d)
- 18
Description
### What happens?
Despite having set `pg_use_binary_copy=true`, if the target table has a column with `NUMERIC` type then text format is used (instead of binary) when copying data from DuckDB to PostgreSQL.
### To Reproduce
1. Configure `duckdb-postgres` as follows:
- `SET pg_debug_show_queries=true;`
- `SET pg_use_binary_copy=true;`
2. Create a DuckDB table: `CREATE OR REPLACE TABLE mytable (id INTEGER, value NUMERIC);`.
3. Insert a row into the table created at step 2: `INSERT INTO mytable VALUES (1, 1.234);`.
4. Open a connection to PostgreSQL: `ATTACH 'dbname= user= password= host=' AS pg (TYPE postgres);`.
5. Create a PostgresSQL table:
```
CALL postgres_execute('pg', '
DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable (
id INTEGER,
value NUMERIC
);'
);
```
6. Copy data from DuckDB to PostgreSQL: `INSERT INTO pg.mytable (id, value) SELECT id, value FROM mytable;`. The following line would show up in debug messages: `COPY "public"."mytable" FROM STDIN (FORMAT TEXT, NULL ')`.
7. Repeat step 5 by replacing `NUMERIC` with `FLOAT`.
8. Repeat step 6. Now, data would be copied using the binary format (debug message: `COPY "public"."mytable" ("id", "value") FROM STDIN (FORMAT BINARY)`).
### OS:
Debian 12
### PostgreSQL Version:
17.5
### DuckDB Version:
v1.4.0-dev2508
### DuckDB Client:
Python
### Full Name:
Alessandro Cerioni
### Affiliation:
État de Genève
### Have you tried this on the latest `main` branch?
- [x] I agree
### Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- [x] I agree
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.