Postgresql – How to “cascade delete” children without deleting parent

cascadepostgresqlpostgresql-11

I have a table, let's call it parent:

CREATE TABLE parent (
    id          TEXT PRIMARY KEY,
    name        TEXT,
    description TEXT,
    -- other parent attributes
    created_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

It has a number of child tables, let's say child_1, child_2, … child_n. They all look something like:

CREATE TABLE child_n (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parent_id TEXT NOT NULL REFERENCES parent (id) ON DELETE CASCADE,
    -- other columns of child_n attributes
);

Some of these children may even have child tables of their own.

When I update a row in parent, I often want to also replace all of its descendants (ie: its children, their children, and so on recursively), as they are only relevant to the older version of that parent row.

My plan was to rely on that REFERENCES parent (id) ON DELETE CASCADE, but that only works if I actually delete the parent. I know I can delete and insert in a transaction, but this makes preserving the values of columns (like created_at) more awkward.

Is there a way to trigger the cascading delete of children without actually deleting the parent row?

Best Answer

Simply re-insert deleted records:

WITH cte AS ( DELETE
              FROM parent
              WHERE {conditions}
              RETURNING * )
INSERT INTO parent
SELECT *
FROM cte;

fiddle

Related Question