CODEWITHSUNDEEP
postgresql
dang
dang

Reputation: 2422

Truncate all tables in Postgres with a suffix

I have a set of tables with suffix _ABC

The table names are like:

TEST_ABC
MYTB_ABC
AB_1_ER_ABC

I know I can do:

TRUNCATE TABLE TEST_ABC;

But I don't want to run this 100s of times.

I have 100s of those. Is there a way using plain SQL(only 1 query) to truncate tables with suffix _ABC?

Upvotes: 0

Views: 1599

Answers (1)

user330315
user330315

Reputation:

You need dynamic SQL for this. This can be done with an anonymous PL/pgSQL block:

do
$$
declare
  l_sql text;
begin
  select 'truncate '||string_agg(tablename, ',')||' cascade'
     into l_sql
  from pg_tables
  where tablename like '%\_abc' escape '\'
    and schemaname = 'public';
  execute l_sql;
end;
$$

Upvotes: 1

Related Questions