Skip to content

Instantly share code, notes, and snippets.

@k4nar
Created July 1, 2020 14:00
Show Gist options
  • Save k4nar/7cebb97cfa89d244b1644156092c8895 to your computer and use it in GitHub Desktop.
Save k4nar/7cebb97cfa89d244b1644156092c8895 to your computer and use it in GitHub Desktop.
SQL - Remove every row in a table using DELETE instead of TRUNCATE
CREATE OR REPLACE FUNCTION nuke() RETURNS void AS $$
DECLARE stmt text;
BEGIN
SELECT 'WITH ' || string_agg('_' || table_name || ' AS (DELETE FROM ' || table_name || ')', ', ') || ' SELECT 1'
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type <> 'VIEW'
INTO stmt;
EXECUTE stmt;
END$$
LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment