I need to remove all the records from my database but I want to keep everything else intact like the tables, constraints, foreign keys and indexes. I’m working with PostgreSQL right now but I’m wondering if there’s a standard SQL command that works across different database systems. I’ve tried looking this up but most solutions I found either drop the entire database or just work for specific tables. Is there a clean way to wipe all the data at once without having to go through each table manually? Any help would be great!
yeah, TRUNCATE works gr8! Just do TRUNCATE TABLE table1, table2 CASCADE; in Postgres. it’ll clear data but keep the structure. for all tables, a lil script can help to auto-generate TRUNCATE for each table ya got.
what about foreign key constraints tho? i’ve hit issues where truncate fails due to referential integrity. do u handle dependencies in a specific order or does CASCADE take care of everything? also curious about ur experience with larger schemas!
Here’s a PostgreSQL script that’ll truncate all tables at once:
SELECT 'TRUNCATE TABLE ' || string_agg(quote_ident(tablename), ', ') || ' RESTART IDENTITY CASCADE;' FROM pg_tables WHERE schemaname = 'public';
Run this query first - it’ll generate the actual truncate command. CASCADE handles foreign key constraints automatically, and RESTART IDENTITY resets your auto-increment sequences. I’ve used this on production databases during migrations without issues.
The command wipes all user tables but keeps your schema structure intact - indexes, constraints, triggers, everything stays. Just copy the output and run it as a separate command.