-1

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?

Edison-Myplanet
  • 11
  • 2
  • 1
    This is a duplicate, when the following questions are combined: [Delete from a dynamic table name in postgres](https://stackoverflow.com/questions/52489754/delete-from-a-dynamic-table-name-in-postgres) and [How do I delete a fixed number of rows with sorting in PostgreSQL?](https://stackoverflow.com/questions/5170546/how-do-i-delete-a-fixed-number-of-rows-with-sorting-in-postgresql) – Luuk Sep 22 '22 at 11:19

1 Answers1

0

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-Myplanet
  • 11
  • 2
  • This presupposes `id` is the primary key. You may want to pull against the constraints table in cases where that is not the case. – Hambone Sep 22 '22 at 15:51