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

Question

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?

This is a duplicate, when the following questions are combined: Delete from a dynamic table name in postgres and How do I delete a fixed number of rows with sorting in PostgreSQL? — Luuk
– Luuk, Commented Sep 22, 2022 at 11:19

Edison-Myplanet · Accepted Answer · 2022-09-22 12:33:29Z

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

answered Sep 22, 2022 at 12:33

Edison-Myplanet

113 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hambone Over a year ago

This presupposes id is the primary key. You may want to pull against the constraints table in cases where that is not the case.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related