duckdb / duckdb/duckdb-postgres
MERGE INTO PostgreSQL table does not apply target column defaults for omitted columns
- Dominant language
- C++
- Stars
- 372
- Forks
- 105
- Avg merge
- 10h 19m
- Merged PRs (30d)
- 18
Description
### What happens?
When using the PostgreSQL extension, `MERGE INTO` fails to apply PostgreSQL column defaults for omitted columns during the `INSERT` branch. This does not happen with a plain `INSERT` statement.
### To Reproduce
The target table has an autoincrement `id` column with an underlying sequence (in postgres).
SQL steps are executed using the Python client like so:
```python
import duckdb
mem = duckdb.connect()
mem.sql("ATTACH '/path/to/my/duckdb.duckdb' AS duck;")
mem.sql("ATTACH 'host=127.0.0.1 port=5432 user=fbftooling dbname=my_db password=...' AS pg (TYPE postgres);")
mem.sql("INSERT INTO ...")
```
An `INSERT` omitting the autoincrement `id` column from DuckDB works correctly:
```sql
INSERT INTO pg.my_table(name, ...)
SELECT name, ...
FROM duck.my_table;
```
PostgreSQL generates the `id` values as expected.
However, the equivalent `MERGE` fails:
```sql
MERGE INTO pg.my_table AS t
USING duck.my_table AS s
ON lower(t.name) = lower(s.name)
WHEN NOT MATCHED THEN
INSERT (name, ...)
VALUES (s.name, ...);
```
This results in:
```text
ERROR: null value in column "id" of relation "my_table" violates not-null constraint
CONTEXT: COPY my_table, line 1
```
It appears that `MERGE` sends an explicit `NULL` for omitted columns instead of allowing PostgreSQL to apply the column default.
Attempting to explicitly use the PostgreSQL sequence also fails:
```sql
INSERT (id, name)
VALUES (nextval('my_table_id_seq'), s.name)
```
DuckDB reports:
```text
Catalog Error: Sequence with name my_table_sequence_id does not exist!
```
This indicates that `nextval()` is evaluated by DuckDB rather than PostgreSQL, so it is not a viable workaround.
**Expected behavior**
`MERGE` should behave consistently with `INSERT`:
* omitted target columns should use PostgreSQL defaults,
* or otherwise preserve PostgreSQL's normal `INSERT` semantics for default values.
### OS:
Windows 11 (WSL 2)
### PostgreSQL Version:
17.10
### DuckDB Version:
1.5.2
### DuckDB Client:
Python
### Full Name:
Leonardo Pedri
### Affiliation:
Innerspace
### 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
Research direction
Reproduce the MERGE and plain INSERT comparison using the Python client with attached DuckDB and PostgreSQL databases. Trace the PostgreSQL extension's MERGE INSERT branch and add a regression test showing that omitted target columns receive PostgreSQL defaults; done when MERGE matches INSERT without evaluating PostgreSQL sequences in DuckDB.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, postgresql, python, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100