How to test run an UPDATE statement in PostgreSQL?
SqlDatabasePostgresqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 - Sql
Wrap it in a transaction, test the results with a SELECT and rollback at the end.
BEGIN;
UPDATE ...;
SELECT ...;
ROLLBACK;
Solution 3 - Sql
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.
Solution 4 - Sql
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 5 - Sql
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;
Solution 6 - Sql
Run the same check with a SELECT statement first: the rows returned by SELECT will be the rows modified by UPDATE
Solution 7 - Sql
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;