Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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)
);

INSERT INTO temp (seqnr,defn_id,attr_id,input) VALUES
  (1,100,100,'test1')
, (2,100,100,'test2')
, (3,100,100,'test3')
, (4,100,100,'test4')
RETURNING *
;

CREATE TABLE




seqnr
defn_id
attr_id
input


1
100
100
test1

2
100
100
test2

3
100
100
test3

4
100
100
test4



INSERT 0 4


-- raises exception with default (non-deferrable) PK:
UPDATE temp SET seqnr = seqnr+1
WHERE defn_id = 100 AND attr_id = 100 AND seqnr >= 1;

ERROR:  duplicate key value violates unique constraint "pk_id"
DETAIL:  Key (defn_id, attr_id, seqnr)=(100, 100, 2) already exists.


-- using deferrable PK instead:
ALTER TABLE temp
  DROP CONSTRAINT pk_id
, ADD  CONSTRAINT pk_id PRIMARY KEY (defn_id, attr_id, seqnr) DEFERRABLE
;

-- Now it just works:
UPDATE temp SET seqnr = seqnr+1
WHERE defn_id = 100 AND attr_id = 100 AND seqnr >= 1;

ALTER TABLE



UPDATE 4


TABLE temp ORDER BY seqnr;


seqnr
defn_id
attr_id
input


2
100
100
test1

3
100
100
test2

4
100
100
test3

5
100
100
test4



SELECT 4


-- revert back to non-deferrable PK:
ALTER TABLE temp
  DROP CONSTRAINT pk_id
, ADD  CONSTRAINT pk_id PRIMARY KEY (defn_id, attr_id, seqnr)
;

ALTER TABLE


-- this workaround 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);

UPDATE 4


TABLE temp ORDER BY seqnr;


seqnr
defn_id
attr_id
input


3
100
100
test1

4
100
100
test2

5
100
100
test3

6
100
100
test4



SELECT 4