Error "duplicate key value violates unique constraint" while updating multiple rows

Question

I created a table in PostgreSQL and Oracle as

CREATE TABLE temp(
   seqnr smallint NOT NULL,
   defn_id int not null,
   attr_id int not null,
   input CHAR(50) NOT NULL,
   CONSTRAINT pk_id PRIMARY KEY (defn_id, attr_id, seqnr)
);

This temp table has primary key as (defn_id,attr_id,seqnr) as a whole!

Then I inserted the record in the temp table as

INSERT INTO temp(seqnr,defn_id,attr_id,input)
VALUES (1,100,100,'test1');

INSERT INTO temp(seqnr,defn_id,attr_id,input)
VALUES (2,100,100,'test2');

INSERT INTO temp(seqnr,defn_id,attr_id,input)
VALUES (3,100,100,'test3');

INSERT INTO temp(seqnr,defn_id,attr_id,input)
VALUES (4,100,100,'test4');

INSERT INTO temp(seqnr,defn_id,attr_id,input)
VALUES (5,100,100,'test5');

in both Oracle and Postgres! The table now contains:

 seqnr |  defn_id | attr_id | input
     1 |      100 |    100  | test1
     2 |      100 |    100  | test2
     3 |      100 |    100  | test3
     4 |      100 |    100  | test4
     5 |      100 |    100  | test5

When I run the command:

UPDATE temp SET seqnr=seqnr+1
WHERE defn_id = 100 AND attr_id = 100 AND seqnr >= 1;

In case of ORACLE it is Updating 5 Rows and the O/p is

 seqnr |  defn_id | attr_id | input
     2 |      100 |    100  | test1
     3 |      100 |    100  | test2
     4 |      100 |    100  | test3
     5 |      100 |    100  | test4
     6 |      100 |    100  | test5

But in case of PostgreSQL it is giving an error!

DETAIL:  Key (defn_id, attr_id, seqnr)=(100, 100, 2) already exists.

Why does this happen and how can I replicate the same result in Postgres as Oracle?
Or how can the same result be achieved in Postgres without any errors?

Have you tried wrapping this in a transaction? You can see why this happens, I presume. When it tried to update row 1, there's already a row with `seqnr==2`. Perhaps if you make it a transaction, it can do the whole chunk at once. — Tim Roberts, May 02 '22 at 04:56
BEGIN; UPDATE temp SET seqnr=seqnr+1 WHERE defn_id = 100 AND attr_id = 100 AND seqnr >= 1; COMMIT; tried but same error persists! — anant, May 02 '22 at 05:04
Also why it is not the Case with Oracle? In oracle it is allowing me to update without any unique constrain violation Error! — anant, May 02 '22 at 05:07
Unrelated, but: [don't use char](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_char.28n.29) (this is a bad choice in Oracle as well) — , May 02 '22 at 05:30
This is because Oracle and Postgres validate a unique constraint differently. The usual solution in Postgres is to define the unique constraint as deferrable. But then you can't define a foreign key to that PK any more. Not sure if that is a problem in your case or not. https://dbfiddle.uk/?rdbms=postgres_14&fiddle=8f6ef832faef38b39823abf23777dfdb — , May 02 '22 at 05:33

Erwin Brandstetter · Accepted Answer · 2022-05-02T05:59:15.970

UNIQUE an PRIMARY KEY constraints are checked immediately (for each row) unless they are defined DEFERRABLE - which is the solution you demand.

ALTER TABLE temp
  DROP CONSTRAINT pk_id
, ADD  CONSTRAINT pk_id PRIMARY KEY (defn_id, attr_id, seqnr) DEFERRABLE
;

Then your UPDATE just works.

db<>fiddle here

This comes at a cost, though. The manual:

Note that deferrable constraints cannot be used as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause.

And for FOREIGN KEY constraints:

The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

And:

When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.

See:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

I would avoid a DEFERRABLE PK if at all possible. Maybe you can work around the demonstrated problem? This usually works:

UPDATE temp t
SET    seqnr = t.seqnr + 1
FROM  (
   SELECT defn_id, attr_id, seqnr
   FROM   temp
   WHERE  defn_id = 100 AND attr_id = 100 AND seqnr >= 1
   ORDER  BY defn_id, attr_id, seqnr DESC
   ) o
WHERE (t.defn_id, t.attr_id, t.seqnr)
    = (o.defn_id, o.attr_id, o.seqnr);

db<>fiddle here

But there are no guarantees as ORDER BY is not specified for UPDATE in Postgres.

UPDATE with ORDER BY

They have an additional drawback: a deferrable primary key can also not be used as the target of a foreign key. — , May 02 '22 at 05:51
i have defn_id and attr_id as foreign key in another tables! so will that cause an issue? — anant, May 02 '22 at 05:55
@a_horse_with_no_name: Yes, important restriction. I added another quote from the manual. And a possible workaround for the `UPDATE`. — Erwin Brandstetter, May 02 '22 at 06:00
@anant: you can't have only defn_id and attr_id as foreign keys - your foreign key must include **all** columns of the primary key — , May 02 '22 at 06:01
@anant: I suspect your FK constraints actually point in the other direction (and that is not impaired either way), wouldn't make sense otherwise. — Erwin Brandstetter, May 02 '22 at 06:03
UPDATE temp SET seqnr = (seqnr + 1) * -1 WHERE defn_id = 100 AND attr_id = 100; UPDATE temp SET seqnr = seqnr * -1 WHERE defn_id = 100 AND attr_id = 100 AND seqnr < 0; — anant, May 02 '22 at 06:12

Error "duplicate key value violates unique constraint" while updating multiple rows

1 Answers1