Postgresql基础实践教程（十）UPSERT

PostgreSQL UPSERT：使用 INSERT ON CONFLICT 语句

说明

INSERT ON CONFLICT 实现的 UPSERT 在任何 PostgreSQL 数据库上都能用，所以你在任何运行 Postgres 的地方都可以套用这些模式。

在本教程中，你将学习如何使用 PostgreSQL 的 upsert 功能——当行不存在时插入新行，当行已存在时更新现有行。

PostgreSQL UPSERT 语句简介

Upsert 是 [update] 和 [insert] 的组合词。它能让你更新已有的行，或者在该行不存在时插入新行。

PostgreSQL 没有专门的 UPSERT 语句，但可以通过 INSERT...ON CONFLICT 语句实现 upsert 操作。

如果你使用 PostgreSQL 15 或更高版本，还可以用 [MERGE] 语句，它和 UPSERT 语句功能相当。

INSERT...ON CONFLICT 语句的基本语法如下：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET column1 = value1, column2 = value2, ...;

语法说明：

• table_name：要插入数据的表名。
• (column1, column2, ...)：要插入值的列列表。
• VALUES(value1, value2, ...)：要插入到指定列 (column1, column2, ...) 的值。
• ON CONFLICT (conflict_column)：指定冲突目标，通常是可能导致冲突的 [唯一约束]或 [唯一索引]。
• DO NOTHING：发生冲突时什么都不做。
• DO UPDATE：发生冲突时执行更新。
• SET column = value1, column = value2, ...：冲突时要更新的列及其对应的值。

INSERT ... ON CONFLICT 语句的工作原理：

首先，ON CONFLICT 子句识别冲突目标（通常是唯一约束或唯一索引）。如果插入的数据违反了约束，就会发生冲突。

其次，DO UPDATE 告诉 PostgreSQL 在发生冲突时更新现有行或什么都不做，而不是中止整个操作。

最后，SET 子句定义要更新的列和值。你可以使用新值，也可以用 EXCLUDED 关键字引用尝试插入的值。

PostgreSQL UPSERT 示例

下面的语句创建 inventory 表并 [插入数据]：

CREATE TABLE inventory(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO inventory(id, name, price, quantity)
VALUES
	(1, 'A', 15.99, 100),
	(2, 'B', 25.49, 50),
	(3, 'C', 19.95, 75)
RETURNING *;

输出：

id | name | price | quantity
----+------+-------+----------
  1 | A    | 15.99 |      100
  2 | B    | 25.49 |       50
  3 | C    | 19.95 |       75
(3 rows)


INSERT 0 3

inventory 表包含各种产品信息，包括名称、价格和库存数量。

假设你收到了一份更新后的产品列表，包含新价格，现在需要相应地更新库存。

这种情况下，upsert 操作就能方便地处理以下场景：

• 更新现有产品：如果产品已在 inventory 表中，用新信息更新价格和数量。
• 插入新产品：如果产品不在 inventory 表中，将其插入表中。

1) 基本的 PostgreSQL INSERT … ON CONFLICT 示例

下面的例子使用 INSERT ... ON CONFLICT 语句向 inventory 表插入新行：

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'A', 16.99, 120)
ON CONFLICT(id)
DO UPDATE SET
  price = EXCLUDED.price,
  quantity = EXCLUDED.quantity;

输出：

INSERT 0 1

本例中，我们尝试向 inventory 表插入新行。

但 inventory 表已经有 id 为 1 的行了，因此发生冲突。

DO UPDATE 将产品的价格和数量更新为要插入的新值。EXCLUDED 允许你访问这些新值。

下面的语句验证更新结果：

SELECT * FROM inventory
WHERE id = 1;

输出：

id | name | price | quantity
----+------+-------+----------
  1 | A    | 16.99 |      120
(1 row)

2) 插入数据示例

下面的例子使用 INSERT ... ON CONFLICT 语句向 inventory 表插入新行：

INSERT INTO inventory (id, name, price, quantity)
VALUES (4, 'D', 29.99, 20)
ON CONFLICT(id)
DO UPDATE SET
  price = EXCLUDED.price,
  quantity = EXCLUDED.quantity;

输出：

INSERT 0 1

由于产品 id 4 不存在于 inventory 表中，该语句 [插入了新行]。

下面的语句验证插入结果：

SELECT * FROM inventory
ORDER BY id;

输出：

id | name | price | quantity
----+------+-------+----------
  1 | A    | 16.99 |      120
  2 | B    | 25.49 |       50
  3 | C    | 19.95 |       75
  4 | D    | 29.99 |       20
(4 rows)

总结

• 使用 PostgreSQL upsert 功能在数据已存在时更新，不存在时插入。
• 使用 INSERT...ON CONFLICT 语句实现 upsert。