By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table if not exists demo (
id serial primary key,
a int not null,
b int not null,
current boolean not null,
constraint one_per unique (a, current)
);
INSERT INTO demo (a, b, current) VALUES (1, 101, true);
1 rows affected
-- ON CONFLICT ON CONSTRAINT one_per DO UPDATE SET current = false WHERE demo.id = EXCLUDED.id ;
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 () ;
INSERT INTO demo (a, b, current)
VALUES (1, 102, true)
1 rows affected
select * from demo
| id | a | b | current |
|---|---|---|---|
| 1 | 1 | 101 | f |
| 2 | 1 | 102 | t |