UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
SELECT 3, 'C', 'Z'
WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);
WITH upsert AS (
UPDATE spider_count SET tally=tally+1
WHERE date='today' AND spider='Googlebot'
RETURNING *
)
INSERT INTO spider_count (spider, tally)
SELECT 'Googlebot', 1
WHERE NOT EXISTS (SELECT * FROM upsert)
CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$
DECLARE
BEGIN
UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2;
IF NOT FOUND THEN
INSERT INTO tableName values (value, arg1, arg2);
END IF;
END;
$$ LANGUAGE 'plpgsql';
3 回答
这已被问过很多次了 . 可在此处找到可能的解决方案:https://stackoverflow.com/a/6527838/552671
此解决方案需要
UPDATE
和INSERT
.使用Postgres 9.1,可以使用一个查询来执行此操作:https://stackoverflow.com/a/8702291/552671
我在这种情况下发现this post更相关:
如果
INSERTS
很少见,我会避免做NOT EXISTS (...)
,因为它会在所有更新中发出SELECT
. 相反,看看wildpeaks答案:https://dba.stackexchange.com/questions/5815/how-can-i-insert-if-key-not-exist-with-postgresql这样Postgres最初会尝试做
UPDATE
. 如果没有行受到影响,它将回退到发出INSERT
.