"There are no UNIQUE/PRIMARY KEY Indexes that refer to this table" when inserting into a PG table with a unique constraint.

[kilianc]

What happens?

Inserting into a postgres table with a conflict or replace statement does not work as it appears duckdb does not fetch the indexes or schema correctly?

To Reproduce

INSTALL postgres;
LOAD postgres;

ATTACH '...'  AS dbname (TYPE POSTGRES);
INSERT OR REPLACE INTO dbname.table ...;

-- Binder Error: There are no UNIQUE/PRIMARY KEY Indexes that refer to this table, ON CONFLICT is a no-op

OS:

Ubuntu 22.04

DuckDB Version:

1.1.3

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Kilian Ciuffolo

Affiliation:

Roblox

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

Did you include all relevant data sets for reproducing the issue?

No - I cannot share the data sets because they are confidential

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[ralexx]

Identical issue using Python binding for DuckDB 1.1.3, PostgreSQL 17.2, macOS 15.2.

In my case I am using Postgres syntax

duckdb.sql("INSERT INTO attached_db.schema.table ON CONFLICT DO NOTHING")

[780Farva]

I'm also running into this issue. Using DuckDB 1.1.3, Postgres 15, and Ubuntu 20.04 via WSL 2 over Windows 10.

I'm using the CLI in a comparable way to the code in the original post.

[Mytherin]

This is an expected restriction currently - see #189