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
INSERT 0 4
CREATE TABLE
| a | b | d |
|---|---|---|
| null | null | 2024-01-11 |
| a | null | 2024-01-11 |
| null | b | 2024-01-11 |
| c | b | 2024-01-11 |
SELECT 4
| id | ref_a | ref_b |
|---|
SELECT 0
ALTER TABLE
INSERT 0 1
INSERT 0 1
| a | b | d |
|---|---|---|
| null | null | 2024-01-11 |
| a | null | 2024-01-11 |
| null | b | 2024-01-11 |
| c | b | 2024-01-11 |
SELECT 4
| id | ref_a | ref_b |
|---|---|---|
| 1 | null | b |
| 2 | null | x |
SELECT 2
ERROR: MATCH PARTIAL not yet implemented LINE 2: ...B_Fk FOREIGN KEY (ref_a, ref_b) REFERENCES A(a,b) MATCH PART... ^
DELETE 2
ALTER TABLE
ALTER TABLE
ERROR: insert or update on table "b" violates foreign key constraint "b_fk" DETAIL: MATCH FULL does not allow mixing of null and nonnull key values.
INSERT 0 1
| a | b | d |
|---|---|---|
| null | null | 2024-01-11 |
| a | null | 2024-01-11 |
| null | b | 2024-01-11 |
| c | b | 2024-01-11 |
SELECT 4
| id | ref_a | ref_b |
|---|---|---|
| 1 | null | null |
SELECT 1