`INSERT INTO [...] ON CONFLICT` unique constraint not found

[dpprdan]

What happens?

INSERT INTO [...] ON CONFLICT does not find unique/primary key constraint for a referenced conflict target in an attached Postgres DB. Hence, the Upsert does not occur.

To Reproduce

Running

INSTALL postgres; LOAD postgres;
ATTACH 'host=localhost user=postgres password=postgres' AS pg (TYPE postgres);

CREATE TABLE IF NOT EXISTS pg.inventory(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO pg.inventory(id, name, price, quantity)
VALUES
    (1, 'A', 15.99, 100),
    (2, 'B', 25.49, 50),
    (3, 'C', 19.95, 75);

INSERT INTO pg.inventory (id, name, price, quantity)
VALUES (1, 'A', 16.99, 120)
ON CONFLICT(id) 
DO UPDATE SET
  price = EXCLUDED.price,
  quantity = EXCLUDED.quantity;

SELECT * FROM pg.inventory 
WHERE id = 1;

DROP TABLE IF EXISTS pg.inventory;

as cat .\duckdb\test_upsert.sql | duckdb returns

Error: near line 17: Binder Error: The specified columns as conflict target are not referenced by a UNIQUE/PRIMARY KEY CONSTRAINT
┌───────┬─────────┬───────────────┬──────────┐
│  id   │  name   │     price     │ quantity │
│ int32 │ varchar │ decimal(10,2) │  int32   │
├───────┼─────────┼───────────────┼──────────┤
│     1 │ A       │         15.99 │      100 │
└───────┴─────────┴───────────────┴──────────┘

This is also relevant for multicolumn and simple unique constraints.

OS:

Windows 11 23H2

PostgreSQL Version:

PostgreSQL 16.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924, 64-bit

DuckDB Version:

v0.10.0 20b1486d11

DuckDB Client:

CLI

Full Name:

Daniel Possenriede

Affiliation:

Analyse & Konzepte immo.analytics

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

[dpprdan]

Whoops,

INSTALL postgres; LOAD postgres;
ATTACH 'host=localhost user=postgres password=postgres' AS pg (TYPE postgres);

was missing above, the example should be complete now.

[Mytherin]

Thanks for reporting! This is currently an expected restriction - we don't support upserts yet.