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 person (
   id serial primary key,
   name text,
   accNum text UNIQUE
);

CREATE FUNCTION accnum_trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   NEW.accNum := right(extract(year from current_date)::text, 2) || NEW.id::text;
   
   RETURN NEW;
END
$$;

CREATE TRIGGER tr_accnum
  BEFORE INSERT
  ON person
  FOR EACH ROW
  EXECUTE PROCEDURE accnum_trigger_function();
INSERT INTO person(name) VALUES ('A'), ('B'), ('C');

SELECT * FROM person
3 rows affected



id
name
accnum


1
A
211

2
B
212

3
C
213