下面是如何使用参数而不使用特殊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

也许会有帮助

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

在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.

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

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

我的解决方案(类似于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

UPDATE将返回修改的行数。如果使用JDBC (Java)，则可以检查该值是否为0，如果没有行受到影响，则触发INSERT。如果使用其他编程语言，可能仍然可以获得修改的行数，请检查文档。

这可能不那么优雅，但您可以从调用代码中使用更简单的SQL。不同的是，如果您用PL/PSQL编写十行脚本，您可能应该单独为它进行一种或另一种单元测试。

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

在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的后续报道