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

[LeonardoPedri]

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:

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:

INSERT INTO pg.my_table(name, ...)
SELECT name, ...
FROM duck.my_table;

PostgreSQL generates the id values as expected.

However, the equivalent MERGE fails:

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:

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:

INSERT (id, name)
VALUES (nextval('my_table_id_seq'), s.name)

DuckDB reports:

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?

• 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?

• I agree

[hiSandog]

The MERGE insert projection should send only the columns named by the branch, allowing PostgreSQL to evaluate defaults, rather than materializing omitted columns as explicit NULLs in the COPY row. Regression coverage should include a sequence-backed default, a general expression default, a nullable column with no default, and a generated/identity column. Comparing the emitted column list with plain INSERT would keep the two paths aligned.