我在管理帐户设置时遇到了与名称值对相同的问题。 设计标准是不同的客户端可以有不同的设置集。

我的解决方案(类似于JWP)是批量擦除和替换，在应用程序中生成合并记录。

这是非常防弹的，平台无关的，因为每个客户端从来没有超过20个设置，这只是3个相当低负载的db调用——可能是最快的方法。

更新单个行的替代方案——检查异常然后插入——或者两者的某种组合是丑陋的代码，缓慢且经常中断，因为(如上所述)非标准SQL异常处理从一个db更改到另一个db——甚至从一个版本更改到另一个版本。

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

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的答案。

就我个人而言，我已经设置了一个附加到插入语句的“规则”。假设你有一个“dns”表，记录每个客户每次的dns点击量:

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

您希望能够重新插入具有更新值的行，或者在这些行不存在时创建它们。输入customer_id和时间。就像这样:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

更新:如果同时进行插入，则有可能失败，因为它将生成unique_violation异常。但是，未终止的事务将继续并成功，您只需要重复已终止的事务。

然而，如果一直都有大量的插入发生，你会想在插入语句周围放一个表锁:SHARE ROW EXCLUSIVE锁将阻止任何可能在目标表中插入、删除或更新行的操作。但是，不更新唯一密钥的更新是安全的，因此如果没有操作将这样做，则使用建议锁。

此外，COPY命令不使用RULES，因此如果使用COPY进行插入，则需要使用触发器。

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

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

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

我在管理帐户设置时遇到了与名称值对相同的问题。 设计标准是不同的客户端可以有不同的设置集。

我的解决方案(类似于JWP)是批量擦除和替换，在应用程序中生成合并记录。

这是非常防弹的，平台无关的，因为每个客户端从来没有超过20个设置，这只是3个相当低负载的db调用——可能是最快的方法。

更新单个行的替代方案——检查异常然后插入——或者两者的某种组合是丑陋的代码，缓慢且经常中断，因为(如上所述)非标准SQL异常处理从一个db更改到另一个db——甚至从一个版本更改到另一个版本。

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION

编辑:这不能正常工作。与接受的答案不同，当两个进程并发重复调用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');