Updating key constraints on multiple records simultaneously-CodePudding

We have a table with a unique key which gets updated by ‘aging’ older records, as mentioned by @Tony O’Hagan here.

The table looks as follows:

-- auto-generated definition
create table abc
(
    key   uuid    not null,
    hash  text    not null,
    age   integer not null,
    value varchar(50),
    constraint abc_pkey
        primary key (key, age)
);

We can simulate an ‘aged’ record with the following dummy data:

INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 0, '1');
INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 1, '2');
INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 2, '3');

When I want to add a new record, I must first ‘age’ the older records before inserting a new record with age=0

However I get the following error message when I run the query below:

[23505] ERROR: duplicate key value violates unique constraint "abc_pkey" Detail: Key (key, age)=(bec619bb-451c-49d8-b555-4d16e1f724fb, 2) already exists.

UPDATE abc
SET age = age  1
WHERE key IN (
    'bec619bb-451c-49d8-b555-4d16e1f724fb'
    )

How can I update/age these records?

CodePudding user response：

We can disable the CONSTRAINTS with the commande

SET CONSTRAINTS ALL DEFERRED

✓

which lets us run our update

UPDATE public.abc SET age = age   1;

3 rows affected

we can then reactivate the CONSTRAINTS with

SET CONSTRAINTS ALL IMMEDIATE

✓