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?
CodePudding user response:
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