I've been googling and trying different queries but I haven't managed to get the UPSERT query to work with both mySql and postgreSql. Does anyone here know how to do it?
我一直在搜索和尝试不同的查询，但我还没有让UPSERT查询同时使用mySql和postgreSql。这里有人知道怎么做吗?
1 个解决方案
#1
4
There is not one, and cannot be one. PostgreSQL doesn't have an UPSERT statement. The new 9.5 statement INSERT ... ON CONFLICT UPDATE ... is syntactically different from MySQL's INSERT ... ON DUPLICATE KEY UPDATE ... because it's also semantically different, i.e. it works differently.
没有，也不可能有。PostgreSQL没有UPSERT语句。新的9.5语句插入…冲突的更新…与MySQL的插入方式不同…对于重复的密钥更新…因为它在语义上是不同的，也就是说它的工作方式不同。
The newer PostgreSQL statement, added in 9.5, was designed with an awareness of MySQL's syntax. The decision not to use the same syntax was intentional because MySQL's statement has big ambiguities around handling of multiple unique indexes etc, where it basically shrugs and says "dunno".
在9.5中添加的更新后的PostgreSQL语句是通过对MySQL语法的了解而设计的。决定不使用相同的语法是有意的，因为MySQL的语句在处理多个独特的索引方面有很大的歧义，在这里，它基本上耸耸肩，说“dunno”。
You can possibly hide it behind stored functions, but really, this is why your application should be able to run different SQL on a different DBMS. Trying to always write one statement that works for all target DBMSes is a lost cause even if you're just targeting MySQL and PostgreSQL.
您可以将它隐藏在存储函数后面，但实际上，这就是为什么应用程序应该能够在不同的DBMS上运行不同的SQL。即使只是针对MySQL和PostgreSQL，也要始终编写一个适用于所有目标dbms的语句。
#1
4
There is not one, and cannot be one. PostgreSQL doesn't have an UPSERT statement. The new 9.5 statement INSERT ... ON CONFLICT UPDATE ... is syntactically different from MySQL's INSERT ... ON DUPLICATE KEY UPDATE ... because it's also semantically different, i.e. it works differently.
没有，也不可能有。PostgreSQL没有UPSERT语句。新的9.5语句插入…冲突的更新…与MySQL的插入方式不同…对于重复的密钥更新…因为它在语义上是不同的，也就是说它的工作方式不同。
The newer PostgreSQL statement, added in 9.5, was designed with an awareness of MySQL's syntax. The decision not to use the same syntax was intentional because MySQL's statement has big ambiguities around handling of multiple unique indexes etc, where it basically shrugs and says "dunno".
在9.5中添加的更新后的PostgreSQL语句是通过对MySQL语法的了解而设计的。决定不使用相同的语法是有意的，因为MySQL的语句在处理多个独特的索引方面有很大的歧义，在这里，它基本上耸耸肩，说“dunno”。
You can possibly hide it behind stored functions, but really, this is why your application should be able to run different SQL on a different DBMS. Trying to always write one statement that works for all target DBMSes is a lost cause even if you're just targeting MySQL and PostgreSQL.
您可以将它隐藏在存储函数后面，但实际上，这就是为什么应用程序应该能够在不同的DBMS上运行不同的SQL。即使只是针对MySQL和PostgreSQL，也要始终编写一个适用于所有目标dbms的语句。