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 parent (
parent_id integer PRIMARY KEY
, txt text
);
CREATE TABLE child (
child_id integer PRIMARY KEY
, parent_id integer REFERENCES parent(parent_id) ON DELETE RESTRICT -- make sure even this works
, txt text
);
INSERT INTO parent VALUES
(1, 'foo')
, (2, 'bar')
;
INSERT INTO child VALUES
(1, 1, 'foo')
, (2, 2, 'bar')
, (3, 2, 'baz') -- parent_id 2 has two children
;
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
-- Delete child 1, cascades
WITH x AS (
DELETE FROM child
WHERE child_id = 1
RETURNING parent_id, child_id
)
DELETE FROM parent p
USING x
WHERE p.parent_id = x.parent_id
AND NOT EXISTS (
SELECT FROM child c
WHERE c.parent_id = x.parent_id
AND c.child_id <> x.child_id
);
DELETE 1
-- Delete child 2, doesn't cascade
WITH x AS (
DELETE FROM child
WHERE child_id = 2
RETURNING parent_id, child_id
)
DELETE FROM parent p
USING x
WHERE p.parent_id = x.parent_id
AND NOT EXISTS (
SELECT FROM child c
WHERE c.parent_id = x.parent_id
AND c.child_id <> x.child_id
);
DELETE 0
SELECT * FROM parent;
SELECT * FROM child;
| parent_id | txt |
|---|---|
| 2 | bar |
SELECT 1
| child_id | parent_id | txt |
|---|---|---|
| 3 | 2 | baz |
SELECT 1