Categories
Mastering Development

Is there an automatic way to delete many rows matching a given condition from many tables?

I have a Postgres database containing many tables, each containing many rows, with many relations of all types between these different tables.

If I add a new column should_delete to all of these tables, is there some automatic way I can have all rows WHERE should_delete = true deleted, without having to manually specify each table that rows should be deleted from, or manually order queries so that rows are deleted before other rows referencing them (and so avoid errors due to constraints failing, e.g. by deleting a row referenced by a foreign key before the row containing the foreign key, even if both rows contain should_delete = true and so should be deleted)?

This should ultimately result in all rows marked should_delete = true being deleted, and no rows marked should_delete = false being deleted, but should only result in an error if there’s no way to delete only rows marked should_delete = true without violating a constraint.

If there’s no pure Postgres way to achieve this, I am also using Python and Django to access this database, and so it would also be acceptable if there is a Python package that could be used to do this.

Leave a Reply

Your email address will not be published. Required fields are marked *