Insert on conflict with conflict target incorrectly calculates constraint

[PadenZach]

What happens?

After ALTER TABLE ... DROP COLUMN, INSERT ... ON CONFLICT (cols) DO NOTHING against a Postgres-backed table fails with:

Binder Error: The specified columns as conflict target are not referenced by a UNIQUE/PRIMARY KEY constraint or index

The dropped column is not part of the conflict target, and the underlying Postgres unique constraint still exists and still references the same columns.

To Reproduce

1. Start Postgres in Docker

docker run -d --name pg-bug \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=postgres \
  -p 5432:5432 \
  postgres:17

# wait for it to come up
until docker exec pg-bug pg_isready -U postgres >/dev/null 2>&1; do sleep 0.5; done

2. Create a table where attnums won't stay 1..N after a drop

The bug needs the dropped column to be physically before one of the conflict-target columns, so its attnum gap shifts the mapping. Easiest way: create the columns in the order d, e, a, b, c so attnums are d=1, e=2, a=3, b=4, c=5, then put the unique constraint on (a, b, c) (conkey {3,4,5}), then drop e.

docker exec -i pg-bug psql -U postgres <<'SQL'
CREATE TABLE tbl (
    d INT,
    e INT,
    a INT,
    b INT,
    c INT,
    UNIQUE (a, b, c)
);
SQL

3. Confirm ON CONFLICT works before the drop

duckdb <<'SQL'
INSTALL postgres_scanner;
LOAD postgres_scanner;
ATTACH 'host=localhost port=5432 user=postgres password=postgres dbname=postgres' AS pg (TYPE POSTGRES);
INSERT INTO pg.public.tbl (a, b, c, d, e) VALUES (1, 2, 3, 4, 5)
    ON CONFLICT (a, b, c) DO NOTHING;
SELECT * FROM pg.public.tbl;
SQL

Expected: insert succeeds, one row returned.

4. Drop column e and re-run the same INSERT (without e)

docker exec -i pg-bug psql -U postgres -c "ALTER TABLE tbl DROP COLUMN e;"

duckdb <<'SQL'
INSTALL postgres_scanner;
LOAD postgres_scanner;
ATTACH 'host=localhost port=5432 user=postgres password=postgres dbname=postgres' AS pg (TYPE POSTGRES);
INSERT INTO pg.public.tbl (a, b, c, d) VALUES (1, 2, 3, 4)
    ON CONFLICT (a, b, c) DO NOTHING;
SQL

Observed:

Binder Error: The specified columns as conflict target are not referenced by a
UNIQUE/PRIMARY KEY CONSTRAINT or INDEX

The Postgres-side constraint is still there — \d tbl in psql will show UNIQUE (a, b, c) is intact and a direct INSERT ... ON CONFLICT (a, b, c) DO NOTHING from psql works fine. The failure is purely in how duckdb_postgres decodes conkey.

OS:

macos

PostgreSQL Version:

17

DuckDB Version:

1.5.2

DuckDB Client:

CLI, Python

Full Name:

Zach Paden

Affiliation:

Symetra

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

[PadenZach]

Created a draft PR with just a test that demonstrates this for now.

Will try and sit down and think of a good solution in the future. If there are any maintainers that have a suggestion I'd be happy to hear it (not used to writing c++ myself).