Write a SQL script to cascade deleting all tables on a PostgreSQL database hosted on…
- User
Write a SQL script to cascade deleting all tables on a PostgreSQL database hosted on Supabase
Assistant
It's important to note that performing a cascade delete on all tables in a PostgreSQL database can result in the permanent loss of all data. This operation should be executed with extreme caution, ideally in a development or testing environment, and only after ensuring that you have backups of any critical data.
Here's a SQL script that will generate and execute the necessary
DROP TABLEstatements withCASCADEto delete all tables in a PostgreSQL database. This script uses dynamic SQL to build theDROP TABLEcommands for each table:DO $$ DECLARE r RECORD; BEGIN -- Loop through all tables in the current schema FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP -- Dynamically build and execute the DROP TABLE command with CASCADE EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE'; END LOOP; END $$;Explanation:
DO $$ ... $$;: This is a PostgreSQL anonymous code block that allows you to execute procedural code.DECLARE r RECORD;: This declares a record variablerto hold each row returned by the query.FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP: This loop iterates over all tables in thepublicschema. You can modify theschemanameif your tables are in a different schema.EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';: This dynamically builds and executes aDROP TABLEcommand for each table with theCASCADEoption, which ensures that dependent objects are also dropped.
Important Considerations:
- Backup Your Data: Ensure you have a complete backup of your database before running this script.
- Test in a Safe Environment: Run this script in a development or testing environment first to ensure it behaves as expected.
- Permissions: Ensure you have the necessary permissions to drop tables in the database.
- Foreign Key Constraints: The
CASCADEoption will drop tables that have foreign key dependencies, but be cautious of complex dependency chains.
If you need to target tables in a different schema, modify the
WHERE schemaname = 'public'clause accordingly.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.