duckdb / duckdb/duckdb-postgres

MERGE INTO PostgreSQL table does not apply target column defaults for omitted columns

Open
#487 1 comment 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.