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

Question

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.

Answer 1

Try following select to get list of tables which contain your column

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'should_delete'
ORDER BY table_schema, table_name;

loop over the result and proceed with your delete statement for each table

I took inspiration from this post: https://kb.objectrocket.com/postgresql/postgres-list-tables-with-python-1023

The second possibility is to generate SQL file with actual delete statements when you get the result from the select - something like this:

with open("delete_statements.sql", "w") as myFile:
    for table in resultFromPostgresqlSelect:
        myFile.write("DELTE FROM {} WHERE should_delete = true;\n".format(table))

EDIT - after few comments and clarifications

What you want is to connect to PostgreSQL using python and run following query

SELECT
    tc.table_schema,
    tc.constraint_name,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE constraint_type = 'FOREIGN KEY';

It will give you following list of foreign keys and list of tables where they are - and what is more important - column names which references to those tables

 table_schema | constraint_name | table_name |     column_name      | foreign_table_schema | foreign_table_name | foreign_column_name
--------------+-----------------+------------+----------------------+----------------------+--------------------+---------------------
 public       | fk_table_a_id   | table_b    | reference_table_a_id | public               | table_a            | id

From this you go to through each foreign_table (in this case table_a) and do select of all IDs where should_delete=true and then do the same for your referenced tables to this table. Once you have those lists (in my case two lists - one list of IDs from table_a and one list of IDs from table_b) you will join them and find only those which are on both sides - those rows you can delete from both tables

In my case i would generate those two SQLs and get result for them

SELECT id FROM table_a WHERE should_delete = true;

SELECT reference_table_a_id FROM table_b WHERE should_delete = true;

Then i can match those IDs and i have list for deletion from table_a and table_b

Or even better - you generate delete query which will have joins between tables - see following post for details PostgreSQL delete with inner join

This is pythonic/dynamic way how to solve your problem. Let me know if you have any questions/suggestions

---

adding definition of those two tables below

CREATE TABLE table_a (id INT GENERATED ALWAYS AS IDENTITY, something int, should_delete int DEFAULT 0,
PRIMARY KEY(id));

CREATE TABLE table_b (id INT GENERATED ALWAYS AS IDENTITY, something_else int, should_delete int DEFAULT 0, reference_table_a_id INT,
PRIMARY KEY(ID), 
CONSTRAINT fk_table_a_id
 FOREIGN KEY(reference_table_a_id) 
      REFERENCES table_a(id)
 );