Postgres 13

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 types (
  id smallint GENERATED ALWAYS AS IDENTITY
, type text UNIQUE
, updated_at timestamptz
);

CREATE TABLE foo (
  id smallint GENERATED ALWAYS AS IDENTITY
, type text UNIQUE
, my_ts timestamptz
);

CREATE OR REPLACE FUNCTION update_column()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW := json_populate_record(NEW, json_build_object(TG_ARGV[0], now()));
   RETURN NEW;
END
$func$;

CREATE TRIGGER column_was_updated
BEFORE INSERT OR UPDATE OF type ON types
FOR EACH ROW EXECUTE FUNCTION update_column('updated_at');

CREATE TRIGGER column_was_updated
BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE FUNCTION update_column('my_ts');

CREATE TABLE



CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER



CREATE TRIGGER


INSERT INTO types (type)
VALUES ('type1'), ('type2')
RETURNING *;


id
type
updated_at


1
type1
2024-01-11 01:15:23.855219+00

2
type2
2024-01-11 01:15:23.855219+00



INSERT 0 2


INSERT INTO foo (type)
VALUES ('type1'), ('type2')
RETURNING *;


id
type
my_ts


1
type1
2024-01-11 01:15:23.86217+00

2
type2
2024-01-11 01:15:23.86217+00



INSERT 0 2

id	type	updated_at
1	type1	2024-01-11 01:15:23.855219+00
2	type2	2024-01-11 01:15:23.855219+00

id	type	my_ts
1	type1	2024-01-11 01:15:23.86217+00
2	type2	2024-01-11 01:15:23.86217+00