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 FUNCTION refresh_updated_at_step1() RETURNS trigger AS
$$
BEGIN
IF NEW.updated_at = OLD.updated_at THEN
NEW.updated_at := NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION refresh_updated_at_step2() RETURNS trigger AS
$$
BEGIN
IF NEW.updated_at IS NULL THEN
NEW.updated_at := OLD.updated_at;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION refresh_updated_at_step3() RETURNS trigger AS
$$
BEGIN
IF NEW.updated_at IS NULL THEN
NEW.updated_at := CURRENT_TIMESTAMP;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE users(
name TEXT NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER refresh_users_updated_at_step1
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
| name | updated_at |
|---|---|
| A | 2020-01-01 00:00:00+00 |
INSERT 0 1
| name | updated_at |
|---|---|
| B | 2020-01-01 00:00:00+00 |
UPDATE 1
| name | updated_at |
|---|---|
| C | 2024-07-08 09:45:10.066872+01 |
UPDATE 1