This is a trick which database experts use, because they have learned (often the hard way) that a simple mistype can trash a database…

Before running any database query which can alter the database (UPDATE, DELETE, INSERT, DROP, CREATE, ALTER) begin a database transaction using the BEGIN command;

Then, run your update query.

Then, check the results.

Then, either use the ROLLBACK command if you aren't happy with the results, or use the COMMIT command if you are happy with the results.

For instance, if you are wanting to delete a widget from the database you could just use the command DELETE FROM widgets WHERE id=27;. The problem is that it's not uncommon for people to just forget to type the 'WHERE id=27' part, and manage to delete all their widgets.

So, to play safe, you would issue the commands:

BEGIN;
DELETE FROM widgets WHERE id=27;

Now, you can check the results of the query. The PostgreSQL psql program will display the number of rows deleted from the database. So if the response says DELETE 1, you know you have just deleted one row, so you can type COMMIT; to commit the transaction. However, if the response says DELETE 57632 rather than panicking as you would do if you weren't doing this in a transaction, you can simply type ROLLBACK; to undo what you have just done.

After doing the update query, you can also issue SELECT queries or other update queries within the same transaction to check database data or perform more updates. (Note that if you have an error, you will have to ROLLBACK and start again).

Certain other database actions may be queued while you are in the transaction, so try not to make it last too long.