How to test run an UPDATE statement in PostgreSQL?

How can I test an UPDATE statement for example to see if it would work, for example if it would actually update rows etc?

Is there a way to simulate it easily?

Solution 1:

Use a transaction to wrap your update statement and a select query (to test the update) and then always roll it back.

Example:

BEGIN;

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';

SELECT balance FROM accounts WHERE name = 'Alice';

ROLLBACK; -- << Important! Un-does your UPDATE statement above!

A transaction typically ends with a commit but since you're just testing and do not want the changes to be permanent you will just roll back.

Solution 2:

Wrap it in a transaction, test the results with a SELECT and rollback at the end.

BEGIN;

UPDATE ...;

SELECT ...;

ROLLBACK;

Solution 3:

You could always build up a sample database on SQL Fiddle and try out your update statements there.

Full disclosure: I am the author of sqlfiddle.com

Solution 4:

Prepend your SQL UPDATE command with EXPLAIN, and it will tell you how many lines will be affected by your command.

This is much simpler than wrapping your command in a transaction.