等同于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) = ('AAA', 'BBB') 
      WHERE col1='AAB' AND col2='BBA';

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

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

'AAA', 'BBB', 'CCC'
'AAB', 'BBA', 'CCC'

ie col3 在现有行和a之间是相同的要更新的行。

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

如果我正在 INSERT 行中，我会在冲突时使用不用，但在 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:

• 这可能是违反直觉的，但是您应该将DB抛出的错误视为好。 br>
  这仅仅是要获得一个状态，并不意味着应用程序崩溃。


• 对于插入操作，冲突时有另一种选择的操作 （更新或不执行任何操作），因此有一种让您决定的语法是很有意义的。
  
  对于更新，您唯一可以做的就是...什么都不做。
  
  那么，为什么由于没有选择，SQL为什么会让您要求做特定的事情？请记住，数据库报告错误好，因此，让数据库什么都不做，告诉您原因。


• 最后，更新主键是一种不好的做法。< br>
  插入的 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.

虽然我在这一点上，但是请注意，主键没有冲突，查询不会失败

1条记录，带有便捷 ON UPDATE NO ACTION 外键也会使其失败（这仍然比使用 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. 如果您正在交易中，请使用保存点。
   
   这是唯一令人烦恼的DB错误：如果在事务中间得到一个错误，您将开始获得当前事务被中止，命令被忽略，直到事务块结束
   
   希望您不需要回滚整个事务并从头开始重做所有操作。您可以使用下面的代码来摆脱困境。

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屋！