This is not something that’s any big hack or secret, but emptying a database of all data without dropping the structure along with it is one of those tasks that I do just often enough in my development work to be annoying. If you ask me, there should just be a big ol’ “EMPTY” statement you can apply to an entire database.
You *can* empty a database table in PostgreSQL using an unqualified DELETE statement, by the way – but it takes longer because it does a full scan of each table. TRUNCATE just nukes everything – and if you feed it the ‘CASCADE’ keyword, it’ll nuke everything in its path as well. This is nice, because I have a bunch of tables in my database, but I know that there are a relatively small collection of tables that everything else links to, so I can pass about 10 table names to TRUNCATE, and giving it the ‘CASCADE’ argument will wipe out about 2 dozen tables.
While I love writing code that creates stuff, writing code to do demolition is somehow amazingly satisfying as well.
Technorati Tags: postgres, postgresql, sql, database, rdbms, dba
