Created
January 9, 2016 07:59
-
-
Save mayank-io/6c492cf53a546773650c to your computer and use it in GitHub Desktop.
Postgres: Truncate all tables in PUBLIC schema for a given USER
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
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
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.