Postgres 10

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?.

select version();

version

PostgreSQL 10.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

CREATE TABLE x(a INT UNIQUE NOT NULL);
CREATE TABLE y(b INT);

ALTER TABLE y ADD COLUMN c INT NOT NULL
CONSTRAINT y_x_fk_c REFERENCES x (a)   -- if x (a) doens't exist, this will fail!
ON UPDATE CASCADE ON DELETE CASCADE;

INSERT INTO x VALUES (1), (2), (3);
3 rows affected

INSERT INTO y VALUES (45, 2), (76, 3);
2 rows affected

SELECT * FROM x;

a

1

2

3

SELECT * FROM y;

b
c

45
2

76
3

INSERT INTO y VALUES (54, 7);  -- 7 not in x (a)!
ERROR:  insert or update on table "y" violates foreign key constraint "y_x_fk_c"
DETAIL:  Key (c)=(7) is not present in table "x".