PostgreSQL - UPSERT 操作

PostgreSQL - UPSERT 操作

“UPSERT”一词是“UPDATE”（更新）和“INSERT”（插入）的合成词。它描述了如果表中不存在新行则插入新行，或如果已存在则更新现有行的操作。这在数据同步时是一种常见需求。自 PostgreSQL 9.5 起，可以使用 ON CONFLICT 子句优雅地处理此操作。

假设您有一个产品库存表，并且每天接收产品价格的更新数据流。有些产品可能是新的，而另一些则是对现有产品的更新。UPSERT 操作允许您在一个单一的原子步骤中处理此数据流。

语法：INSERT ON CONFLICT

PostgreSQL 中 UPSERT 功能的核心是 ON CONFLICT 子句，您将其添加到 INSERT 语句中。它主要有两种变体。

‘DO UPDATE’ 的语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET
    column1 = EXCLUDED.column1,
    column2 = EXCLUDED.column2, ...;

‘DO NOTHING’ 的语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO NOTHING;

关键组成部分

• conflict_target：指定导致冲突的原因。这可以是一个具有 UNIQUE 约束的列（例如 (product_code)），或是一个特定的约束名称（ON CONSTRAINT constraint_name）。
• DO UPDATE SET：如果发生冲突，将执行此操作。您在此定义如何更新现有行。
• EXCLUDED：这是一个特殊的、临时的、类似表的 (table-like) 对象，它保存了因冲突而未能插入的行的值。您可以使用 EXCLUDED.column_name 来访问这些提议的新值。
• DO NOTHING：此操作指示 PostgreSQL 在检测到冲突时简单地丢弃新行，不执行任何操作。

实际示例

设置：产品库存表

让我们创建一个表来跟踪产品库存和价格。sku（库存单位）将是我们的唯一标识符。

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100),
    price NUMERIC(10, 2),
    stock_count INT
);

INSERT INTO products (sku, name, price, stock_count)
VALUES ('LAP-101', '14-inch Laptop', 1200.00, 50);

示例 1：使用 ON CONFLICT DO UPDATE

我们收到了产品 ‘LAP-101’ 的更新，包含新的价格和库存数量。我们将使用 UPSERT 来应用此更改。

INSERT INTO products (sku, name, price, stock_count)
VALUES ('LAP-101', '14-inch Laptop Pro', 1250.50, 45)
ON CONFLICT (sku)
DO UPDATE SET
    price = EXCLUDED.price,
    stock_count = EXCLUDED.stock_count,
    name = EXCLUDED.name; -- 也更新名称

现在，让我们验证结果。‘LAP-101’ 的现有行应该已更新。

postgres=# SELECT * FROM products WHERE sku = 'LAP-101';
 id |   sku   |         name         |  price  | stock_count
----+---------+----------------------+---------+-------------
  1 | LAP-101 | 14-inch Laptop Pro   | 1250.50 |          45
(1 row)

接下来，让我们插入一个全新的产品。由于 sku 没有冲突，这将是一个标准的 INSERT 操作。

INSERT INTO products (sku, name, price, stock_count)
VALUES ('KBD-202', 'Mechanical Keyboard', 150.00, 200)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price, stock_count = EXCLUDED.stock_count;

表中现在包含两个不同的产品：

postgres=# SELECT * FROM products ORDER BY id;
 id |   sku   |         name         |  price  | stock_count
----+---------+----------------------+---------+-------------
  1 | LAP-101 | 14-inch Laptop Pro   | 1250.50 |          45
  2 | KBD-202 | Mechanical Keyboard  |  150.00 |         200
(2 rows)

示例 2：使用 ON CONFLICT DO NOTHING

这在您只想添加新条目并忽略任何重复项的情况下非常有用。例如，填充 mailing_list 表。

CREATE TABLE mailing_list (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL
);

INSERT INTO mailing_list (email) VALUES ('alpha@example.com');

-- 现在，尝试再次插入相同的电子邮件
INSERT INTO mailing_list (email) VALUES ('alpha@example.com')
ON CONFLICT (email) DO NOTHING;

The INSERT 语句将报告 INSERT 0 0，表示没有插入任何行。表保持不变，也没有抛出错误。

postgres=# SELECT * FROM mailing_list;
 id |       email
----+-------------------
  1 | alpha@example.com
(1 row)

高级技巧：条件更新

您可以在 DO UPDATE 操作中添加 WHERE 子句，使更新成为条件性的。例如，您可能只想在价格低于当前价格时才更新产品价格。

INSERT INTO products (sku, name, price, stock_count)
VALUES ('LAP-101', '14-inch Laptop Pro', 1300.00, 40) -- 价格更高
ON CONFLICT (sku)
DO UPDATE SET
    price = EXCLUDED.price
WHERE
    products.price > EXCLUDED.price; -- 仅在新价格更低时更新

在这种情况下，由于 1250.50 不大于 1300.00，UPDATE 将被跳过，并且该行将保持不变。这提供了对数据同步逻辑的精细控制。