Postgres - 如果找不到记录,则在更新时返回错误 [英] Postgres - Return error on update if record not found
问题描述
我想知道如果我发出更新时数据库中不存在该行,是否有办法在更新时恢复错误.
I am wondering if there is a way of getting an error back on update if the row does not exist in the database when I issue an update.
update users set email='aa@bb.com' where id=200
如果 users 表没有 id=200 的用户,那么 postgres 只会说更新 0".但是,我想知道是否可以返回错误.这样,我不必向数据库发出 2 个请求,一次检查存在,一次更新.如果我们单独进行检查存在性和发布更新之间也可能存在竞争条件.
If users table does not have user with id=200, then postgres simply says "UPDATE 0". However, I am wondering if I can get an error back. That way, I don't have to issue 2 requests to the database, once to check existence, once to update. There could also be a race condition between checking the existence and issuing an update if we do it separately.
为什么我需要一个错误?很简单 - 让客户知道他们使用了无效的用户 ID 并采取纠正措施.
Why do I need an error? Simple - so the clients know they have used an invalid user id and they take corrective action.
推荐答案
你可以使用类似的东西
update users set email='aa@bb.com' where id=200 returning id;
此查询将返回更新行的 id
.如果它返回 0 行 - 则在您的应用程序中引发错误.
This query will return the id
of the updated row. If it returns 0 rows - then throw an error in your application.
此外,您可能还想检查您的数据库驱动程序/框架是否返回受影响的行数(如 JDBC 中的 getUpdateCount()
).
Also you may want to check if your db driver / framework returns the count of affected rows (like getUpdateCount()
in JDBC).
这篇关于Postgres - 如果找不到记录,则在更新时返回错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!