duckdb / duckdb/duckdb-postgres

Silent switching from binary to text copy

Open
#358 6 comments 1 reaction 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.