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

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 PostgreSQL without any errors?

CodePudding user response：

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

CodePudding user response：

this is because of the internal DB-engine mechanism of Postgresql.

In the case of Oracle DB, it Updates the value in the library cache first and updates the actual data later on.

But in the case of Postgres, the actual data is being updated. And in the presented case you have made sqenr as unique. So, when it adds 1 to the first row that matches the next row value and so it violates the uniqueness.