SQL 如何在 PostgreSQL 中测试运行 UPDATE 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9202011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to test run an UPDATE statement in PostgreSQL?
提问by user1154863
How can I test an UPDATE statement for example to see if it would work, for example if it would actually update rows etc?
例如，如何测试 UPDATE 语句以查看它是否有效，例如它是否会实际更新行等？
Is there a way to simulate it easily?
有没有办法轻松模拟它？
回答by Paul Sasik
Use a transactionto 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 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.
事务通常以提交结束，但由于您只是在测试并且不希望更改是永久性的，因此您只需回滚即可。
回答by Joe Stefanelli
Wrap it in a transaction, test the results with a SELECT and rollback at the end.
将其包装在一个事务中，使用 SELECT 测试结果并在最后回滚。
BEGIN;
UPDATE ...;
SELECT ...;
ROLLBACK;
回答by Jake Feasel
You could always build up a sample database on SQL Fiddleand try out your update statements there.
你总是可以在SQL Fiddle上建立一个示例数据库并在那里尝试你的更新语句。
Full disclosure: I am the author of sqlfiddle.com
完全披露：我是 sqlfiddle.com 的作者
回答by Taichman
回答by adrianlzt
With Postgres you can use the UPDATE clause RETURNINGto show which rows have been modificated.
使用 Postgres，您可以使用 UPDATE 子句RETURNING来显示哪些行已被修改。
-- 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;
回答by onedaywhen
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;
回答by ibiwan
Run the same check with a SELECT statement first: the rows returned by SELECT will be the rows modified by UPDATE
首先使用 SELECT 语句运行相同的检查：SELECT 返回的行将是 UPDATE 修改的行
