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 fruits(
id serial PRIMARY KEY
, name varchar NOT NULL UNIQUE -- !
);
-- trigger function
CREATE FUNCTION trg_next_id_on_update()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
NEW.id := nextval(pg_get_serial_sequence('fruits', 'id'));
RETURN NEW;
END
$func$;
-- trigger
CREATE TRIGGER next_id_on_update
BEFORE UPDATE ON fruits
FOR EACH ROW
WHEN (NEW.name <> OLD.name)
EXECUTE PROCEDURE trg_next_id_on_update();
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT INTO fruits(name) VALUES('Orange');
INSERT INTO fruits(id,name) VALUES(DEFAULT,'Apple');
TABLE fruits;
INSERT 0 1
INSERT 0 1
| id | name |
|---|---|
| 1 | Orange |
| 2 | Apple |
SELECT 2
UPDATE fruits
SET name = 'Orange2'
WHERE name = 'Orange';
UPDATE 1
TABLE fruits;
| id | name |
|---|---|
| 2 | Apple |
| 3 | Orange2 |
SELECT 2