Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

makasitaram commented Jun 4, 2021

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