PostgreSQL：插入前检查行存在，实现按需插入或更新最新行

表结构 #

原插入语句 #

INSERT INTO db.table("customerNumber", "productId", "dateTime", "action") 
VALUES (:customerNumber, :productId, (now() at time zone ('utc')), :action);

需求 #

• 若对应customerNumber的行不存在，则插入新行
• 若存在单个同customerNumber的行，则更新该行
• 若存在多个同customerNumber的行，则仅更新其中dateTime最新的行（如示例中仅更新id=2的行）

解决方案 #

因为customerNumber不是唯一键，ON CONFLICT无法直接使用，这里用CTE（公共表表达式）实现单条语句完成需求：

WITH target_row AS (
    -- 定位对应customerNumber下最新的行
    SELECT id
    FROM db.table
    WHERE "customerNumber" = :customerNumber
    ORDER BY "dateTime" DESC
    LIMIT 1
),
update_result AS (
    -- 更新找到的最新行
    UPDATE db.table
    SET "productId" = :productId,
        "dateTime" = (now() at time zone ('utc')),
        "action" = :action
    WHERE id IN (SELECT id FROM target_row)
    RETURNING 1
)
-- 如果没有更新过（即无对应行），则插入新行
INSERT INTO db.table("customerNumber", "productId", "dateTime", "action")
SELECT :customerNumber, :productId, (now() at time zone ('utc')), :action
WHERE NOT EXISTS (SELECT 1 FROM update_result)
  AND NOT EXISTS (SELECT 1 FROM db.table WHERE "customerNumber" = :customerNumber);

逻辑说明 #

1. target_row：通过ORDER BY dateTime DESC LIMIT 1精准找到当前customerNumber下最新的那一行ID
2. update_result：执行更新操作，返回1表示更新成功（有匹配行）
3. 最后的INSERT仅在两种情况触发：一是没有找到任何可更新的行（update_result为空），二是当前表中完全没有该customerNumber的记录，确保无行时插入新数据

这样就完美覆盖了你提出的三个需求，用单条SQL语句完成操作。

内容的提问来源于stack exchange，提问作者Essej