Skip to content

Instantly share code, notes, and snippets.

@mayank-io
Created January 9, 2016 07:59
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save mayank-io/6c492cf53a546773650c to your computer and use it in GitHub Desktop.
Save mayank-io/6c492cf53a546773650c to your computer and use it in GitHub Desktop.
Postgres: Truncate all tables in PUBLIC schema for a given USER
-- Running this snippet creates a function. This function can then be executed
-- in this manner:
-- SELECT truncate_tables('postgres');
CREATE OR REPLACE FUNCTION truncate_tables(_username text)
RETURNS void AS
$func$
BEGIN
RAISE NOTICE '%',
-- EXECUTE -- dangerous, test before you execute!
(SELECT 'TRUNCATE TABLE '
|| string_agg(quote_ident(schemaname) || '.' || quote_ident(tablename), ', ')
|| ' CASCADE'
FROM pg_tables
WHERE tableowner = _username
AND schemaname = 'public'
);
END
$func$ LANGUAGE plpgsql;
@makasitaram
Copy link

Didn't work for me, I have several databases in my server, are we anywhere mentioning about Database name, I tried executing it in a query tool. but the data inside tables for that database is still there.

@imaddi47
Copy link

imaddi47 commented Mar 4, 2022

Didn't work for me, I have several databases in my server, are we anywhere mentioning about Database name, I tried executing it in a query tool. but the data inside tables for that database is still there.

This is a function that is to be added inside each database you got on the server. Because function do live inside a database and you have to execute this function by going into every database.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment