今天在做跨库数据同步时突然发现对方的数据库是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