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