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

Question

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...

You need dynamic SQL for this. TRUNCATE syntax supports only a static list of table names, not a query. — Bergi
– Bergi, Commented Jul 27, 2021 at 14:17

Jim Jones · Accepted Answer · 2021-07-27 14:29:28Z

6

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

edited Jul 27, 2021 at 14:29

answered Jul 27, 2021 at 14:17

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

3 Comments

Luiz E. Over a year ago

amazing! that is what I was looking for. Thanks!

Luiz E. Over a year ago

yeah, just did, couldn't accept earlier because of the SO cooldown :)

Jim Jones Over a year ago

@LuizE. no problem.. happy coding :)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related