plpgsql: change order of update-CodePudding

I've the following table:

CREATE TABLE api.capabilities (
  id uuid NOT NULL,
  historycounter int8 NOT NULL DEFAULT 0,
  url jsonb NOT NULL,
  checkservice bool NOT NULL DEFAULT false,
  added timestamptz NOT NULL,
  modified timestamptz NULL,
  deleted timestamptz NULL,
  adduser varchar(40) NOT NULL DEFAULT 'pgsql'::character varying,
  lastuser varchar(40) NULL,
CONSTRAINT id_histcount PRIMARY KEY (id, historycounter)
);

With the following content:

|80af3ff3-2dc1-434b-ad3c-490d8b4a7949|1|{"host": "dev.33wessling824.net", "protocol": "http"}|false|2022-07-12 18:35:17.465 0200|||pgsql||

|80af3ff3-2dc1-434b-ad3c-490d8b4a7949|0|{"host": "dev.33wessling824.net", "protocol": "http"}|true|2022-07-12 18:35:17.465 0200|2022-07-14 11:46:50.073 0200||pgsql||

I'd like to run this update statement:

Update api.capabilities set historycounter = historycounter   1 where id = '80af3ff3-2dc1-434b-ad3c-490d8b4a7949';

Trying this results in an error duplicate key value violates unique constraint "id_histcount"

Because first the dataset with historycounter = 0 is updated, I do get this error. How to change my code to first update the dataset with the biggest historycounter?

Thanks in advance! Achim

CodePudding user response：

If you are totally sure that your data will not violate primary key after your transaction. First, alter table constraint. This is safe:

ALTER TABLE api.capabilities
ALTER CONSTRAINT id_histcount DEFERRABLE INITIALLY IMMEDIATE;

Then in your transaction:

begin transaction;

set constraints all deferred;
Update api.capabilities set historycounter = historycounter   1 where id = '80af3ff3-2dc1-434b-ad3c-490d8b4a7949';
--your other operations

commit transaction;

Source: https://www.postgresql.org/docs/current/sql-set-constraints.html