UPDATE .. RETURNING doesn't work if there's a PRIMARY KEY or UNIQUE constraint on the table

[lukaseder]

What happens?

I can't use UPDATE .. RETURNING on a table with PRIMARY KEY

To Reproduce

create table t (i int primary key, j int);
insert into t values (1, 1);
update t set j = 2 where i = 1 returning *;

This produces:

Constraint Error: Duplicate key "i: 1" violates primary key constraint

This works, as a workaround:

create table t (i int primary key, j int);
insert into t values (1, 1);
update t set j = 2 where i = 1;
select * from t where i = 1;

Removing the PRIMARY KEY also solves the problem

create table t (i int, j int);
insert into t values (1, 1);
update t set j = 2 where i = 1 returning *;

OS:

Microsoft Windows [Version 10.0.22621.1555]

DuckDB Version:

v0.7.2-dev2430

DuckDB Client:

JDBC

Full Name:

Lukas Eder

Affiliation:

Data Geekery / jOOQ

Have you tried this on the latest master 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

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[edyu]

Even removing foreign key constraints, you'll get a constrain error:

Constraint Error: Duplicate key "id: 5d7739ff-012f-4368-ad52-d6a6a58f2699" violates primary key constraint. If this is an unexpected constraint violation please double check with the known index limitations section in our documentation (docs - sql - indexes).

The current documentation doesn't show RETURNING as part of UPDATE statement so I'd be happier even if you throw out wrong/illegal SQL error than this vague constraint error.
Note that once I remove returning clause, it would work.
see #8988 for more info.

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.