Skip to content
TheChatArchiveThe Chat Archive
Amazon Novaamazon.nova-pro-v1:0· AmazonHugging Face LMArena Human Preference 140K

Write a SQL script to cascade deleting all tables on a PostgreSQL database hosted on…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Geminigemini-2.5-flash-lite-preview-06-17-thinking’s answer.
  1. User

    Write a SQL script to cascade deleting all tables on a PostgreSQL database hosted on Supabase

  2. Amazon Nova 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 TABLE statements with CASCADE to delete all tables in a PostgreSQL database. This script uses dynamic SQL to build the DROP TABLE commands 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:

    1. DO $$ ... $$;: This is a PostgreSQL anonymous code block that allows you to execute procedural code.
    2. DECLARE r RECORD;: This declares a record variable r to hold each row returned by the query.
    3. FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP: This loop iterates over all tables in the public schema. You can modify the schemaname if your tables are in a different schema.
    4. EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';: This dynamically builds and executes a DROP TABLE command for each table with the CASCADE option, 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 CASCADE option 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.