等效于 ON CONFLICT DO NOTHING for UPDATE postgres [英] Equivalent of ON CONFLICT DO NOTHING for UPDATE postgres

问题描述

如果更新的版本不会违反主键约束，我想更新我的 postgres 数据库中的行.如果可以，我想保留该行.

I want to update rows in my postgres database if the updated version wouldn't violate the primary key constraint. If it would, I want to leave the row as it is.

假设表在 col1, col2, col3 上有主键，如果我运行这样的查询:

Assuming the table has primary keys on col1, col2, col3, if I run a query like this:

UPDATE table SET (col1, col2) = ('A', 'B') 
      WHERE col1='D' AND col2='E';

如果存在两个条目，查询将失败，我将收到重复键错误:

The query will fail and I will get a duplicate key error if there exists two entries:

'A', 'B', 'C'
'D', 'E', 'C'

即 col3 在现有行和要更新的行之间是相同的.

i.e col3 is the same between an existing row and a row to be updated.

如果我是 INSERT 行，我会使用 ON CONFLICT DO NOTHING，但我找不到 UPDATE 的实现.是否存在等价物?

If I was INSERTing rows I would use ON CONFLICT DO NOTHING but I can't find an implementation of this for UPDATE. Does an equivalent exist?

推荐答案

AFAIK，没有这样的等价物.

AFAIK, there is no such equivalent.

假设您正在开发一个连接到 postgresql 数据库的应用程序，根据您的问题，您需要牢记以下几点:

Let us say you are developing an application that connects to a postgresql database, there are a few things you need to keep in mind, in the context of your question:

• 这可能违反直觉，但您应该将数据库抛出的错误视为好事.
  这只是获取状态，并不意味着应用程序崩溃.
• 对于插入，可以选择 on conflict 操作(更新或不更新)，因此有一个让您决定的语法是有意义的.
  对于更新，您唯一能做的就是……什么都不做.
  那么既然别无选择，为什么 SQL 会让你要求做一些特定的事情呢?请记住，数据库报告错误是好，所以让数据库什么也不做，告诉你原因.
• 最后，更新主键是一种不好的做法.
  插入的 ON CONFLICT ... 不打算更新主键字段.事实上恰恰相反:它旨在更新单个记录中的所有字段除了来自主键的字段.

• It may be counter-intuitive but you should consider errors being thrown by the DB as a good thing.
  This is just about getting a status, it does not mean application crash.
• For insert, there is an alternative choice of action on conflict (update or nothing) so it makes sense to have a syntax to let you decide.
  For updates, the only thing you can do is ... nothing.
  So why would SQL let you ask to do something specific since there is no choice? Remember that DB reporting errors is good, so let the DB do nothing and tell you why.
• Last, it is a bad practice to update primary keys.
  The ON CONFLICT ... for inserts is not intended to update the primary key fields. The very opposite in fact: it is intended to update all the fields except the ones from the primary key in a single record.

当我谈到这一点时，请注意，查询失败不需要主键冲突
使用方便" ON UPDATE NO ACTION 外键的 1 条记录也会使其失败(这仍然比使用 ON UPDATE CASCADE 更新 50 个表中的 10M+ 记录要好...).顺便说一句，您知道 Oracle 甚至没有 ON UPDATE CASCADE 子句吗?您认为这是什么原因?

While I am on that point, please note that there was no need for a conflict on primary key for the query to fail
1 record with the "convenient" ON UPDATE NO ACTION foreign key would have made it fail too (which is still better than updating 10M+ records in 50 tables with a ON UPDATE CASCADE ...). BTW, did you know Oracle does not even have the ON UPDATE CASCADE clause? What do you think is the reason for that?

在那种情况下你能/不应该做什么?

What can you/should not do in that situation?

1. 不要更新主键，就像我说的那样.您的问题对于 UNIQUE 约束仍然有效，但请不要更新主键.
2. 不要尝试查看是否存在冲突记录.这可能需要很长时间，但仍然不可靠.
   您真的要选择数百万条记录来避免错误代码吗?
   此外，当您扩展到其他约束(CHECK 或 EXCLUSION)时，您是否真的会无误地键入所需的附加代码，以便再次避免错误代码?
   最后，如果您实施了行级安全性，冲突可能来自您看不到的记录.
3. 处理应用中的错误代码.接收状态为良好.
4. 如果您正在进行交易，请使用保存点.
   这是数据库错误唯一令人讨厌的事情:如果您在事务中间遇到一个，您将开始得到当前事务被中止，命令被忽略直到事务块结束.
   希望您不需要回滚整个事务并从头开始重做所有事情.您可以使用以下代码摆脱困境.

1. Do not update the primary key, like I said. Your question is still valid for UNIQUE constraints but please please please, NEVER update primary keys.
2. Do not attempt to see if a conflicting record already exists. It may take a long time and still be unreliable.
   Do you really want to select millions of records just to avoid the error codes?
   Also, when you extend to other constraints (CHECK or EXCLUSION), will you really type the additional code it takes with no error in order to, once again, only avoid an error code?
   Last, if you have implemented row-level security, the conflict may arise from a record you cannot see.
3. Handle the error code in your app. Receiving status is GOOD.
4. Use save points if you are in the middle of a transaction.
   This is the only annoying thing with DB errors: if you get one in the middle of a transaction, you will start getting current transaction is aborted, commands ignored until end of transaction block for everything.
   Hopefully, you do not need to roll the entire transaction back and redo everything from scratch. You can get away using the following piece of code.

给你:

BEGIN;
SAVEPOINT MySavepoint;
UPDATE mytable set myuniquefield = 3; /*2+ records are going to be updated */
rollback to savepoint MySavepoint;
/*Insert Some more queries here*/
COMMIT;

这篇关于等效于 ON CONFLICT DO NOTHING for UPDATE postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！