How to test run an UPDATE statement in PostgreSQL?

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

BEGIN;

UPDATE ...;

SELECT ...;

ROLLBACK;

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.

Prepend your SQL UPDATE command with EXPLAIN, and it will tell you how many lines will be affected by your command. And it will not perform the actual update.

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

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