Postgres 9.6

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 elbat
             (birthyear int);

CREATE TABLE


INSERT INTO elbat
            (birthyear)
            VALUES (1987),
                   (2020);

INSERT 0 2


ALTER TABLE elbat
            ADD CHECK(birthyear > 1990);

ERROR:  check constraint "elbat_birthyear_check" is violated by some row


CREATE FUNCTION check_birthyear
                ()
                RETURNS TRIGGER
AS
$$
BEGIN
  IF NOT new.birthyear > 1990 THEN
    RAISE EXCEPTION 'birthyear must be greater than 1990';
  END IF;
  
  RETURN new;
END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION


CREATE TRIGGER elbat_check_birthyear
               BEFORE INSERT
               ON elbat
               FOR EACH ROW
               EXECUTE PROCEDURE check_birthyear();

CREATE TRIGGER


INSERT INTO elbat
            (birthyear)
            VALUES (1987);

ERROR:  birthyear must be greater than 1990
CONTEXT:  PL/pgSQL function check_birthyear() line 4 at RAISE


UPDATE elbat
       SET birthyear = 1987;

UPDATE 2


SELECT *
       FROM elbat;


birthyear


1987

1987



SELECT 2