"ERROR: duplicate key value violates unique constraint" in PostgreSQL but not in Oracle/SQLServer

In both Oracle and SQL Server, these non-deferrable constraints are evaluated at the statement completion, and it does allow intermediate state which could break constraint rules as long as final state at the statement completion is compliant.

PostgreSQL implements this differently, and the output can depends on the physical data orders.

PostgreSQL Example

mytest=# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.1 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit
(1 row)

mytest=# drop table p;
DROP TABLE
mytest=# create table p (id int primary key);
CREATE TABLE
mytest=# insert into p values(1),(2),(3);
INSERT 0 3
mytest=# select * from p;
 id
----
  1
  2
  3
(3 rows)

mytest=# update p set id=id+1;
ERROR:  duplicate key value violates unique constraint "p_pkey"
DETAIL:  Key (id)=(2) already exists.

If we update the table backwards, it would succeed. As showed in followed code:

mytest=# do
$$
declare
  rec_p record;
  cur_p cursor for
       select id from p order by id desc;
begin
  open cur_p;
  loop
    fetch cur_p into rec_p;
    exit when not found;
      update p set id=id+1 where id=rec_p.id;
  end loop;
end;
$$ language plpgsql;
DO

mytest=# select * from p;
 id
----
  4
  3
  2
(3 rows)

If the data in different physical order, it would succeed too.

mytest=# truncate table p;
TRUNCATE TABLE
mytest=# insert into p values(3),(2),(1);
INSERT 0 3
mytest=# select * from p;
 id
----
  3
  2
  1
(3 rows)

mytest=# update p set id=id+1;
UPDATE 3

mytest=# select * from p;
 id
----
  4
  3
  2
(3 rows)

MS SQL Server Example

[oracle@ol ~]$ sqlcmd  -S localhost -U sa -C -P p_ssw0rd -d TestDB
1> select @@version;
2> go

-------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64)
  Nov 14 2023 18:33:19
  Copyright (C) 2022 Microsoft Corporation
  Developer Edition (64-bit) on Linux (Oracle Linux Server 9.3) <X64>

(1 rows affected)

1> create table p (id int primary key);
2> go

1> select * from t;
2> go

1> select * from p;
2> go
id
-----------

(0 rows affected)

1> insert into p values(1),(2),(3);
2> go

(3 rows affected)

1> update p set id=id+1;
2> go

(3 rows affected)

1> select * from p;
2> go

id
-----------
          2
          3
          4

(3 rows affected)

Oracle Example

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0

SQL> create table p (id int primary key);

Table created.

SQL> insert into p values(1),(2),(3);

3 rows created.

SQL> commit;

Commit complete.

SQL> update p set id=id+1;

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from p;

  ID
----------
   2
   3
   4