PostgreSQL upsert_sql upsert returning csdn-CSDN博客

今天在做跨库数据同步时突然发现对方的数据库是PostgreSQL,而之前没有用过这个数据库。然后到网上找了下资料，结果是一般的增删改查的语句应该基本不用改，但是oracle里面有个merge语句在PostgreSQL关键字不太一样，所以单独拿出来记录下。

碰到这个问题后我首先去百度了下PostgreSQL merge into ,发现找不到没有什么有用的信息，好在还有bing。用bing找了下果然找到了相关的内容。

http://wiki.postgresql.org/wiki/UPSERT

点击打开链接

有一个upsert语句(insert 和update的合体)，语法结构和例子大概是下面这样子：

语法结构:

 [ WITH [ RECURSIVE ] with_query [, ...] ]
 INSERT INTO table_name [ ( column_name [, ...] ) ]
     { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
     [ ON CONFLICT [ ( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] [ WHERE index_predicate ] ) ]
       { IGNORE | UPDATE
         SET { column_name = { expression | DEFAULT } |
               ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
             } [, ...]
         [ WHERE condition ]
       }
     ]
     [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

示例:

 INSERT INTO upsert(key, val) VALUES(10, 'Wombat')
   -- Works with only partial index on "key",
   -- covering "WHERE is_active" (this would also
   -- work if we didn't drop "upsert_pkey" and
   -- create the partial unique index)
   DO ON CONFLICT (key WHERE is_active)
   UPDATE SET val = EXCLUDED.val;

就是INSERT INTO ...  ON CONFLICT .. update... 这种结构。

另外找到的一个博客是:http://blog.csdn.net/rudygao/article/details/50498908