Sv: Primary Key Update issue ?

På fredag 06. september 2019 kl. 11:06:04, skrev Patrick FICHE <
Patrick(dot)Fiche(at)aqsacom(dot)com <mailto:Patrick(dot)Fiche(at)aqsacom(dot)com>>:
Hello,

While doing some testing on a Postgresql database, I encountered a strange
behavior which is very simple to reproduce.

I just wanted to know if this is expected behavior or if it should be
considered as an issue.

The scenario to reproduce it is the following.

CREATE TABLE Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key(
pKey ) );

INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );

INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );

UPDATE Test SET pKey = pKey + 1;

Here is the error that I get.

SQL Error [23505]: ERROR: duplicate key value violates unique constraint
"pk_test"

Detail: Key (pkey)=(2) already exists.

I was expecting pKey to be incremented for each row, which would still respect
the unique constraint….

I’m currently using PostgreSQL 11.5 but have the same problem on PostgreSQL
10.3 server.

Best Regards,
It works if you add "DEFERRABLE INITIALLY DEFERRED" to the PK: CREATE TABLE
Test ( pKey integer, Val integer, CONSTRAINT PK_Test PRIMARY Key( pKey )
DEFERRABLE INITIALLY DEFERRED );
andreak(at)[local]:5432 11.5 test=# CREATE TABLE Test ( pKey integer, Val
integer, CONSTRAINT PK_Test PRIMARY Key( pKey ) DEFERRABLE INITIALLY DEFERRED );
CREATE TABLE
andreak(at)[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 1, 1 );
INSERT 0 1
andreak(at)[local]:5432 11.5 test=# INSERT INTO Test( pKey, Val ) VALUES ( 2, 2 );
INSERT 0 1
andreak(at)[local]:5432 11.5 test=# UPDATE Test SET pKey = pKey + 1;
UPDATE 2
-- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com> www.visena.com
<https://www.visena.com> <https://www.visena.com>