amchugh89 amchugh89 - 1 year ago 43
SQL Question

Reset specific tables with clean data

I am doing a test that updates my database each time I run it

And I cannot do the test again with the updated values

I am recreating the WHOLE database with:

postgres=# drop database mydb;
postgres=# CREATE DATABASE mydb WITH TEMPLATE mycleandb;

This takes a while

Is there any way I can update just the tables that I changed with tables from mycleandb?

Answer Source


You haven't mentioned what your programming language or framework are. Many of them have built in test mechanisms that take care of this sort of thing. If you are not using one of them, what you can do is to start a transaction with each test setup. Then roll it back when you tear down the test.


Rollback, as the name suggests reverses all that has been done to the database so that it remains at the original condition.

There is one small problem with this approach though, you can't do integration tests where you intentionally enter incorrect values and cause a query to fail integrity tests. If you do that the transaction ends and no new statements can be executed until rolled back.


it's possible to use the -t option of pg_dump to dump and then restore one or a few tables. This maybe the next best option when transactions are not practical.

Non Durable Settings / Ramdisk

If both above options are inapplicable please see this answer:

It's on a question about django testing but there's very little django specific stuff on that. However coincidentally django's rather excellent test framework relies on the begin/update/rollback mechanis described above by default.