Deleting rows from multiple table in different schema in PostgreSQL

userWkhh1 0
Viewed 28

I have different schemas in my database and each schema has a minimum of two tables, and each table contains one column name, data. Now I want to delete all the entries from different tables present in the different schemas where data = 'test'.

Below is my database structure

A schema - a table - data column
           b table - data column
B schema - c table - data column
           d table

How can delete the row and which approach needs to be followed to handle this deletion in a single query or store procedure?

psql postgresql
1 Answers

you can get the all schemas ,table names with column names in the same database

with the following query ,in general it will list all tables and views , so filtered with is_updatable='YES'(y bcoz u can update only table columns).

with the below query export the list to a text file and execute it .

SELECT 'delete from  '||table_catalog||'.'||table_schema||'.'||table_name||' where '||column_name||' = ''test'';'
FROM information_schema.columns where "column_name"='data' and is_updatable='YES';

create a procedure and run it with cursor

 FOR CUR_REC IN (
 SELECT 'delete from  '||table_catalog||'.'||table_schema||'.'||table_name||' where '||column_name||' = ''test'';' as delcmd
FROM information_schema.columns where "column_name"='data' and is_updatable='YES'
) LOOP
                        EXECUTE CUR_REC.delcmd  ;
                  END LOOP;

with this cursor loop you can get the list and delete .

Sund'er0
Related