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

• 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

[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.

[matt-bradbury]

Also encountering the same issue!

[johnrc90]

Same problem here.

[jaredlander]

I'm running into the same issue, almost. I understand that UPSERTs are now supported, but it is failing for me even when I specify ON CONFLICT DO NOTHING.

My real goal is to do something like

COPY db.mytable FROM 'myfile.parquet';

but that has even less ability to do conflict resolution.

So I am trying

INSERT INTO db.mytable SELECT * FROM 'myfile.parquet' ON CONFLICT DO NOTHING;

but as stated above that won't work similar to the limitation on upserts.

[klutzer]

Same issue here! We can't use ON CONFLICT and neither the MERGE statement (duckdb/duckdb#4601).

[trungda]

@Mytherin could you point us to the direction on how to implement this? I am quite oblivious but I thought that the extension is simply libpq so we would just passthrough the postgres command to the libpq library and let it handle the reset?

[Mytherin]

It's not so trivial because DuckDB handles the binding, so it's not only about forwarding the SQL. The code to support it should be added here.

[thunwal]

Same problem here. The ability to upsert into Postgres is an important use case in my opinion.