Re: EMPTYING A DATABASE

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
>> I am developing a project in which, of course, information is fed to the DB
>> for testing purposes and sometimes I need to get the DB clean, without any
>> record.
>> What would be the best practice to delete all of the records and start
>> testing with a blank DB?

> Your best bet is to recreate the database. Start with an database that has all
> the tables already emptied out, then make a copy of it. Then drop and
> recreate your database at will using the copy as a template.

That's only going to be convenient if the OP periodically wants to
return to exactly the same database state. If he's doing database
development, it's probably going to be more like he periodically
changes the schema and then wants to start over. I suppose he could
try to remember to apply his schema changes in the template database
and then clone it, but that seems pretty error-prone.

My thought would be to make use of multi-table TRUNCATE, assuming
he's using a PG version new enough to have that. Keep a script
around with a TRUNCATE that lists all of the related tables in one
command, and just run the script to reset.

regards, tom lane