警告:如果同时从多个会话执行，这是不安全的(参见下面的警告)。

在postgresql中执行“UPSERT”的另一个聪明方法是执行两个连续的UPDATE/INSERT语句，每个语句都被设计为成功或没有效果。

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);

如果已经存在“id=3”的行，则UPDATE将成功，否则将不起作用。

只有当“id=3”的行不存在时，INSERT才会成功。

您可以将这两个组合到一个字符串中，并通过从应用程序执行的单个SQL语句同时运行它们。强烈建议在一个事务中同时运行它们。

This works very well when run in isolation or on a locked table, but is subject to race conditions that mean it might still fail with duplicate key error if a row is inserted concurrently, or might terminate with no row inserted when a row is deleted concurrently. A SERIALIZABLE transaction on PostgreSQL 9.1 or higher will handle it reliably at the cost of a very high serialization failure rate, meaning you'll have to retry a lot. See why is upsert so complicated, which discusses this case in more detail.

这种方法在读提交隔离中还可能导致更新丢失，除非应用程序检查受影响的行数，并验证插入或更新是否影响了行。

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进行插入，则需要使用触发器。

在PostgreSQL 9.1中，这可以使用可写的CTE(公共表表达式)来实现:

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)

看看这些博客:

通过可写CTE上传 等待9.1 -可写cte 为什么upsert这么复杂?

请注意，此解决方案不能防止唯一键违反，但不容易丢失更新。 请在dba.stackexchange.com上查看Craig Ringer的后续报道

根据INSERT语句的PostgreSQL文档，不支持处理ON DUPLICATE KEY情况。这部分语法是一个专有的MySQL扩展。

当我来这里的时候，我也在寻找同样的东西，但缺乏通用的“upsert”函数让我有点困扰，所以我认为你可以通过更新和插入sql作为该函数的参数形式手册

它看起来是这样的:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

也许要做你最初想做的事情，批处理“upsert”，你可以使用Tcl分割sql_update并循环各个更新，性能的影响将非常小，参见http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

最高的成本是从您的代码执行查询，在数据库方面的执行成本要小得多