如何在 PostgreSQL 中测试运行 UPDATE 语句？

我如何能够测试一个 UPDATE 语句，例如，看看它是否会工作，例如，它是否会实际更新行等？

有没有一种方法可以很容易地模拟它？

45253 次浏览
小开

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

BEGIN;


UPDATE ...;


SELECT ...;


ROLLBACK;
小开

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

小开
最佳答案

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.

小开

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

小开

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;
小开

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;
小开

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.


提交答案