How to test run an UPDATE statement in PostgreSQL?

Question

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?

Answer 1

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

BEGIN;

UPDATE ...;

SELECT ...;

ROLLBACK;

Answer 2

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.

Answer 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

Answer 4

With Postgres you can use the UPDATE clause RETURNING to show which rows have been modificated.

-- example data
CREATE TABLE data(id int, text text);
INSERT INTO DATA VALUES(1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');

-- original data
SELECT * from data;

-- dry-run update
BEGIN;

UPDATE
  data
SET
  text = 'modified'
WHERE
  id > 2
RETURNING
  id, text;

ROLLBACK;

-- data after dry-run update
SELECT * from data;

Answer 5

Run the same check with a SELECT statement first: the rows returned by SELECT will be the rows modified by UPDATE

Answer 6

Given this simple update:

UPDATE Products
   SET price_including_vat = price * 1.05
 WHERE product_type = 'Food';

I would test it using something like this:

 SELECT price_including_vat AS price_including_vat__before, 
        price * 1.05 AS price_including_vat__after, 
        *
   FROM Products
 WHERE product_type = 'Food';

Actually, I'd proably engage brain and do analysis more like this:

WITH updated AS 
   (
    SELECT price_including_vat AS price_including_vat__before, 
           price * 1.05 AS price_including_vat__after, 
           *
      FROM Products
    WHERE product_type = 'Food'
   )
SELECT * 
  FROM updated
 WHERE price_including_vat__before = price_including_vat__after;