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
Musings of an anonymous geek 
I’m not sure about postgres, but for Oracle databases, when you do a TRUNCATE it does not generate redo or rollback, and it doesn’t create row-level table locks. This makes TRUNCATE work much faster because it doesn’t require near as much disk access.
The downside of this is that you really have to be sure you want to truncate – the only way you can recover the data is by restoring from backup.
Comment by haaseg — May 17, 2007 @ 12:18 pm |
haaseg!!
Yeah, I’m actually not sure about how postgresql handles row-level locking in this context, but I do know that it doesn’t require near the disk i/o that doing a DELETE would require, which is what makes it faster. As for rollbacks, I guess I should’ve said that I’m doing this on a test database on my laptop in my own private, local development environment. I would completely disallow TRUNCATE from being run in production at all… or at least require an act of congress to get it done. That’s one of those things that suits should not even be told exists š
Good to hear from you!
Comment by m0j0 — May 17, 2007 @ 12:41 pm |
Truncate works great in a production environment when you are using partitioning though. Partition your transaction data by month, then create a maintenance job that runs once a month, adding a partition for the next month (we actually add the partition for 2 months out so we have an extra on hand), and then truncating the old partitions (say 3 months or older) that you don’t require anymore. It’s a whole helluva lot cleaner and easier than doing “delete from _table_ where create_date
Comment by haaseg — May 17, 2007 @ 1:05 pm |
how can i truncate a database ?
Comment by battisti — February 29, 2008 @ 4:07 pm |