插入，重复更新PostgreSQL?

几个月前，我从Stack Overflow上的一个回答中学到了如何在MySQL中使用以下语法一次执行多个更新:

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

我现在已经切换到PostgreSQL，显然这是不正确的。它引用了所有正确的表，所以我假设这是使用不同关键字的问题，但我不确定在PostgreSQL文档中这是被覆盖的。

为了澄清，我想插入一些东西，如果它们已经存在，则更新它们。

当前回答

PostgreSQL从9.5版本开始使用UPSERT语法，带有ON冲突子句。使用以下语法(类似于MySQL)

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

在postgresql的电子邮件组档案中搜索“upsert”，可以在手册中找到一个做你可能想做的事情的例子:

38-2示例。UPDATE/INSERT异常下面的例子使用异常处理来执行UPDATE或INSERT:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

在黑客邮件列表中可能有一个如何批量使用9.1及以上版本的cte的示例:

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

有关更清晰的示例，请参阅a_horis_with_no_name的答案。

2009-07-10 12:18:16

其他回答

编辑:这不能正常工作。与接受的答案不同，当两个进程并发重复调用upsert_foo时，会产生唯一键违反。

尤里卡!我想出了一个方法，在一个查询:使用UPDATE…返回测试是否有任何行受到影响:

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

UPDATE必须在单独的过程中完成，因为，不幸的是，这是一个语法错误:

... WHERE NOT EXISTS (UPDATE ...)

现在它按预期工作:

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');

2012-01-02 13:50:23

对于合并小集合，可以使用上面的函数。但是，如果您正在合并大量数据，我建议您查看http://mbk.projects.postgresql.org

目前我所知道的最佳实践是:

复制新的/更新的数据到临时表(当然，或者你可以做INSERT，如果成本是ok) 获得锁[可选](建议优先于表锁，IMO) 合并。(有趣的部分)

2009-07-10 22:57:55

我用这个函数归并

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql

2014-12-03 19:02:50

下面是如何使用参数而不使用特殊sql结构进行upsert的示例如果你有特殊的条件(有时你不能用on conflict，因为你不能创建约束)

WITH upd AS
(
    update view_layer set metadata=:metadata where layer_id = :layer_id and view_id = :view_id returning id
)
insert into view_layer (layer_id, view_id, metadata)
(select :layer_id layer_id, :view_id view_id, :metadata metadata FROM view_layer l 
where NOT EXISTS(select id FROM upd WHERE id IS NOT NULL) limit 1)
returning id

也许会有帮助

2023-02-08 10:20:38