CODEWITHSUNDEEP
postgresqldynamic-sqltruncate
Luiz E.
Luiz E.

Reputation: 7239

truncate all tables in Postgres except for the ones provided in a list

I want to truncate the whole database while maintaining the sequence identity. I came up with something like this:

WITH tables_to_be_truncated AS (
SELECT table_name
    FROM information_schema.tables
    WHERE table_type='BASE TABLE'
    AND table_schema='public'
    AND table_name NOT IN ('admins', 'admin_roles')
)
TRUNCATE TABLE (SELECT table_name FROM tables_to_be_truncated) CONTINUE IDENTITY RESTRICT;

I get this error:

ERROR:  syntax error at or near "TRUNCATE"
LINE 9: TRUNCATE TABLE (SELECT table_name FROM tables_to_be_truncated...

I do have the permissions to truncate the tables and when I run for a single table like TRUNCATE TABLE access_tokens it works fine.

I also tried with this

TRUNCATE TABLE (SELECT string_agg(table_name, ', ') FROM tables_to_be_truncated) CONTINUE IDENTITY RESTRICT

which didn't work as well.

From what I see in other posts, people are doing it with functions. I didn't want to go down this path honestly but if this is the only way...

Upvotes: 2

Views: 4381

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

You don't need a function for that. An anonymous code block will do:

DO $$
DECLARE row RECORD;
BEGIN
  FOR row IN SELECT table_name
    FROM information_schema.tables
    WHERE table_type='BASE TABLE'
    AND table_schema='public'
    AND table_name NOT IN ('admins', 'admin_roles') 
  LOOP 
    EXECUTE format('TRUNCATE TABLE %I CONTINUE IDENTITY RESTRICT;',row.table_name);
  END LOOP;
END;
$$;

Other than that I don't think you'll be able to run dynamic queries with pure SQL.

Demo: db<>fiddle

Upvotes: 6

Related Questions