How to truncate all tables that start with the same prefix - Postgresql

Carol Vieira 0
Viewed 22

With the code:

SELECT * 
FROM information_schema.tables 
WHERE table_name LIKE 'prefix%';

I can obtain all the tables that I want to truncate, but how can I execute the TRUNCATE TABLE function for the results of that SELECT statement?

information-schema truncate select sql postgresql
1 Answers

You cannot execute TRUNCATE from a SELECT statement. You could do something like

SELECT concat('TRUNCATE TABLE ',table_catalog,'.',table_schema,'.',table_name)
FROM information_schema.tables 
WHERE table_name LIKE 'prefix%';

which would return a TRUNCATE statement for each table with the prefix. You could even look into using a stored procedure to execute each single row.

boski0
Related