22 May 2013

当客户端可以保证不会出现并发的时候,可以使用PostgreSQL的writeable CTE实现upsert功能。如果存在并发,writeable会出现race conditions而出现问题,这时就需要使用UDF实现。


WITH new_values (id, field1, field2) as (
values
(1, 'A', 'X'),
(2, 'B', 'Y'),
(3, 'C', 'Z')
),
upsert as
(
update mytable m
set field1 = nv.field1,
field2 = nv.field2
FROM new_values nv
WHERE m.id = nv.id
RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1
FROM upsert up
WHERE up.id = new_values.id)

原链接:http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql/8702291#8702291

相关连接: