Postgresql 增量如果存在或创建一个新行

Question

你好，我有一个像这样的简单表格:

+------------+------------+----------------------+----------------+
|id (serial) | date(date) | customer_fk(integer) | value(integer) |
+------------+------------+----------------------+----------------+

如果客户值(value)到达，我想像使用每日累加器一样使用每一行如果不存在该客户和日期的记录，则为该客户和日期创建一个新行，但如果存在则仅增加该值。

我不知道如何实现类似的东西，我只知道如何使用 SET 增加一个值，但这里需要更多的逻辑。提前致谢。
我使用的是 9.4 版

Answer 1

听起来您想要做的是 UPSERT。

http://www.postgresql.org/docs/devel/static/sql-insert.html

在这种类型的查询中，如果记录存在则更新它，如果不存在则创建一个新记录。表中的键将由 customer_fk 和 date 组成。

这将是一个正常的插入，但是 ON CONFLICT DO UPDATE SET value = value + 1。

注意:这仅适用于 Postgres 9.5。这在以前的版本中是不可能的。对于 9.1 之前的版本，唯一的解决方案是两个步骤。对于 9.1 或更高版本，也可以使用 CTE。

对于较早版本的 Postgres，您需要先执行 UPDATE，其中包含 customer_fk 和 date WHERE 子句。从那里检查受影响的行数是否为 0。如果是，则执行 INSERT。唯一的问题是，如果此操作几乎同时发生两次(在 Web 环境中很常见)，则可能会出现竞争条件，因为 INSERT 有可能其中之一失败你的计数总是有可能略有偏差。

如果您使用的是 Postgres 9.1 或更高版本，您可以使用此处巧妙指出的可更新 CTE:Insert, on duplicate update in PostgreSQL?此解决方案不太可能导致竞争条件，因为它是一步执行的。

WITH new_values (date::date, customer_fk::integer, value::integer) AS (
  VALUES 
     (today, 24, 1)
),
upsert AS ( 
    UPDATE mytable m 
        SET value = value + 1
    FROM new_values nv
    WHERE m.date = nv.date AND m.customer_fk = nv.customer_fk
    RETURNING m.*
)
INSERT INTO mytable (date, customer_fk, value)
SELECT date, customer_fk, value
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.date = new_values.date
                  AND up.customer_fk = new_values.customer_fk)

这包含两个 CTE 表。一个包含您要插入的数据 (new_values)，另一个包含使用这些值 (upsert) 的 UPDATE 查询的结果。最后一部分使用这两个表检查 new_values 中的记录是否不存在于 upsert 中，这将意味着 UPDATE 失败，并执行INSERT 来创建记录。

---

附带说明一下，如果您在另一个符合标准的 SQL 引擎中执行此操作，则应改用 MERGE 查询。 [ https://en.wikipedia.org/wiki/Merge_(SQL) ]