主页 / dba / 问题 / 261093
Accepted
cycero
Asked: 2020-03-04 05:53:54 +0800 CST

Postgres 中的 SQL UPSERT

  • 772

我有以下 SQL：

UPDATE table_name as t 
  set value = c.value 
from (values(10, 'key_1', 60),(11, 'key_2', 10)) as c(id, key, value) 
where t.id = c.id and t.key = c.key

这仅在表中已经存在给定的“id”和“key”的情况下才有效。但是，如果数据库中存在“id”和“key”，我需要使其更新值，如果不存在，则使用给定的“id”、“key”和“value”插入新行。

我把这个例子作为一个 UPSERT 工作，但只适用于一组值：

WITH upsert AS (
 UPDATE table_name 
   SET value=80 
 WHERE id=10 
   AND key='key_1' RETURNING *
) 
INSERT INTO table_name (id, key, value) 
SELECT 10, 'key_1', 10 
WHERE NOT EXISTS (SELECT * FROM upsert);

我不确定如何将其合并到我的案例中以使用多个值（第一个示例与 UPDATE）。

我没有唯一的约束或索引(id, "key")

postgresql upsert

1 个回答

  1. Best Answer

    利用insert ... on conflict

    insert into table_Name 
  (id, "key", value) 
values 
  (10, 'key_1', 60),
  (11, 'key_2', 10)
on conflict (id, "key") do update  
  set value = excluded.value;

    这假设您有一个唯一的键（或索引）table_name(id, "key")

    如果没有唯一约束或索引，您可以这样做：

    WITH new_values (id, "key", value) as (
  values
  (10, 'key_1', 60),
  (11, 'key_2', 10)

), upsert as (
    update the_table t
        set value = c.value
    FROM new_values nv
    WHERE t.id = nv.id 
      AND t."key" = nv."key"
    RETURNING t.*
)
INSERT INTO the_table (id, "key", value)
SELECT nv.id, nv."key", nv.value
FROM new_values nv
WHERE NOT EXISTS (SELECT 1
                  FROM upsert up
                  WHERE up.id = nv.id
                    AND up."key" = nv."key")
;

    以上对于并发插入/更新是不安全的- 你仍然可以结束重复。避免这些重复的唯一可靠方法是使用唯一约束或索引。

    • 3

相关问题