PostgreSQL list all tables and DELETE limited rows from each table (loop)

Edison-Myplanet 0
Viewed 29

I'm trying to select all tables in my PSQL DB and then delete 100 rows from each of them

-- select all the tables
With table_names as
(SELECT relname FROM pg_stat_user_tables ORDER BY n_live_tup DESC) 
-- delete from each of the tables
DELETE FROM x_table_name WHERE x_table_name IN (SELECT * FROM table_names);

This gives me an error: Error: relation "x_table_name" does not exist

and when I add LIMIT 100, to delete only 100 rows, it errors Syntax error at or near 'LIMIT'

How can I correct this query without complicating it?

postgresql
1 Answers

Solved:

-- select tables names
-- loop through tables names
-- delete a fixed number of rows from each table
DO $$
DECLARE
v_rec RECORD;
BEGIN
for v_rec IN (SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC
LIMIT 100)
LOOP
  EXECUTE format('DELETE FROM '||v_rec.schemaname || '.' ||v_rec.relname ||' WHERE id IN (SELECT id FROM '||v_rec.schemaname || '.' ||v_rec.relname ||' LIMIT 100)');
END LOOP;
END;
$$ LANGUAGE plpgsql;

Credit goes to the comment by Luuk

Edison-Myplanet0
Related