Can PostgreSQL's update the other row on INSERT conflict?

February 16, 2022

I have a table which records which is the most recently inserted b for the given a:


CREATE TABLE IF NOT EXISTS demo (
    id       serial primary key,
    a        int not null,
    b        int not null,
    current  boolean not null
);

CREATE UNIQUE INDEX ON demo (a, current) WHERE CURRENT = true;


INSERT INTO demo (a, b, current) VALUES (1, 101, true);

I want to be able to insert values and when they conflict, the new row should be updated and the prior, conflicting, row should be updated.

E.g. I have

select * from demo;
 id | a |  b  | current
----+---+-----+---------
  1 | 1 | 101 | t

Then I run something like:

INSERT INTO demo (a, b, current)
       VALUES (1, 102, true)
       ON CONFLICT SET «THE OTHER ONE».current = false;

and then I would see:

select * from demo;
 id | a |  b  | current
----+---+-----+---------
  1 | 1 | 101 | f        <- changed
  2 | 1 | 102 | t

Is there syntax in PostgreSQL that allows this?

>Solution :

As proposed by @Adrian, you can do it with a trigger :

CREATE OR REPLACE FUNCTION before_insert ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
  UPDATE demo
     SET current = false
   WHERE a = NEW.a ;
  RETURN NEW ;
END ;
$$ ;

CREATE TRIGGER before_insert BEFORE INSERT ON demo
FOR EACH ROW EXECUTE FUNCTION before_insert () ;

see test result in dbfiddle

PS : the constraint one_per will prevent from having several former rows for the same a value and with current = false