CTE based insert of multiple rows into "one-per-group" table violates unique index

Question

I have a table where only one row per group can be true. This is enforced by a partial unique index (which can't be deferred).

CREATE TABLE test
(
    id        SERIAL PRIMARY KEY,
    my_group  INTEGER,
    last      BOOLEAN DEFAULT TRUE
);

CREATE UNIQUE INDEX "test.last" ON test (my_group) WHERE last;

INSERT INTO test (my_group)
VALUES (1), (2);

I'm trying to insert a new row into this table that shall replace the "last" element of the corresponding group.
I also want to accomplish this in a single statement.

With some CTE trickery I'm able to do this: link to Fiddle

-- the statement is structured this way to closely resemble my actual usecase
WITH
new_data AS (
    VALUES (1)
),
uncheck_old_last AS (
    UPDATE test
    SET last = FALSE
    WHERE last AND my_group in (SELECT * FROM new_data)
    RETURNING TRUE
)
INSERT INTO test (my_group)
SELECT *
FROM new_data
WHERE COALESCE((SELECT * FROM uncheck_old_last LIMIT 1), true);

So far so good, the insert happens... no conflicts.
I don't quite understand why this is working as from my understanding all CTEs should read the same initial DB state and can't see the changes made by other CTEs

The problem is now that I get a unique violation when I try to do the same with multiple rows at once: Link to Fiddle

-- the statement is structured this way to closely resemble my actual usecase
WITH
new_data AS (
    VALUES (1), (2) -- <- difference to above query
),
uncheck_old_last AS (
    UPDATE test
    SET last = FALSE
    WHERE last AND my_group in (SELECT * FROM new_data)
    RETURNING TRUE
)
INSERT INTO test (my_group)
SELECT *
FROM new_data
WHERE COALESCE((SELECT * FROM uncheck_old_last LIMIT 1), true);

-- Schema Error: error: duplicate key value violates unique constraint "test.last"

Is there any way to insert multiple rows with one statement /
Can someone explain to me why the first query is working and the second isn't?

I would solve this using a BEFORE INSERT trigger, which means you can use standard INSERT statements without having to worry about it. — , Jan 23 '20 at 07:45
A trigger would probably be cleaner, yes. It's just that we don't do triggers in the project I'm working on (I guess to keep all query strings self-contained and make maintenance easier. By now I always prefer a cte solution too, because it's way easier to optimize based on the query plan, with triggers I always have to keep track of which code a query actually executes). But I'll ask the project lead if he would consider using a trigger for this, maybe this is used in more places than I'm aware of and a trigger would actually make sense. — Fabian N., Jan 23 '20 at 08:02
Well, you could simply put two statements (`UPDATE` then `INSERT`) into a transaction then you don't need to fight the semantics of CTEs — , Jan 23 '20 at 08:07
Haha well, you see... the wrapper I'm using doesn't expose pdo transaction functions. I already created a pull request for it to add them but at the moment it's unlikely that we will use it, the project lead doesn't have the time to update the docker image of the server. But thanks for the effort. — Fabian N., Jan 23 '20 at 08:14
Postgres allows sending multiple statements in a in single "go" `update test set ... where ...; insert into ...;` — , Jan 23 '20 at 08:15
Not when you want to use PDO prepared statements :( ... at least I thought so, apparently that's not true https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd but I don't see any example where someone used multiple queries and parameter binding... I'm confused — Fabian N., Jan 23 '20 at 08:27

score 0 · Answer 1 · answered Jan 23 '20 at 07:24

This was caused by PostgreSQL simplifying my always true clause:

WHERE COALESCE((SELECT * FROM uncheck_old_last LIMIT 1), true)

was supposed to create a dependency between the main query and the CTE to enforce execution order from the main query's point of view.

It broke with more than one entry because the limit 1 allowed PostgreSQL to ignore the second row, as only one was required for evaluation.

I fixed it by comparing COUNT(*) > -1 instead:

COALESCE((SELECT COUNT(*) FROM uncheck_old_last) > -1, true)

CTE based insert of multiple rows into "one-per-group" table violates unique index

1 Answers1