PostgreSQL 中 UPSERT 的 ON CONFLICT

FreeGuideOnline 最新 2026-07-06

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


- `conflict_target`：定义唯一约束或排他约束，决定何时触发冲突。
- `conflict_action`：可以是 `DO NOTHING`（跳过插入）或 `DO UPDATE SET ...`（执行更新）。

#### 冲突目标（Conflict Target）

| 冲突目标类型            | 说明                                           | 示例                                 |
|----------------------|----------------------------------------------|--------------------------------------|
| 指定列名                | 基于该列上的唯一约束/主键                          | `ON CONFLICT (id)`                   |
| 指定约束名              | 直接引用约束名称（唯一索引、排他约束等）               | `ON CONFLICT ON CONSTRAINT users_email_key` |
| 部分唯一索引表达式        | 使用 `WHERE` 条件定义的唯一索引                    | `ON CONFLICT (email) WHERE active = true` |

**注意**：`conflict_target` 必须对应一个合法的唯一索引或排他约束，否则语句会报错。

### 两种主要操作模式

#### 1. DO NOTHING（忽略冲突）

当插入的数据与现有记录冲突时，放弃本次插入，不进行任何更新。常用于避免重复数据，无需处理更新逻辑。

**示例表结构**：
```sql
CREATE TABLE products (
    sku TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    stock INT DEFAULT 0
);

插入 SKU 为 'A001' 的商品，如果已存在则什么都不做：

INSERT INTO products (sku, name, stock)
VALUES ('A001', '蓝牙耳机', 10)
ON CONFLICT (sku) DO NOTHING;

执行后若 'A001' 已存在，不会改变其 name 和 stock，语句返回 INSERT 0 0。

2. DO UPDATE SET（存在即更新）

当冲突发生时，更新特定字段。可以通过 EXCLUDED 伪表引用本次 INSERT 中拟写入的值。

INSERT INTO products (sku, name, stock)
VALUES ('A001', '蓝牙耳机', 10)
ON CONFLICT (sku) DO UPDATE SET
    name = EXCLUDED.name,
    stock = products.stock + EXCLUDED.stock;

在这个例子中：

EXCLUDED.name 指向 VALUES 中的 '蓝牙耳机'
products.stock 指向表中已存在的库存值
最终结果：更新商品名，并累加库存

EXCLUDED 详解：

是一个特殊的临时表行，仅在 ON CONFLICT DO UPDATE 的 SET 和 WHERE 子句中可用
包含本次插入尝试的所有字段值
不能用于 DO NOTHING 模式

进阶用法

部分唯一索引与条件冲突

假设我们只想对活跃用户保证邮箱唯一性，允许非活跃用户重复邮箱。可先建立部分唯一索引：

CREATE UNIQUE INDEX users_active_email_idx
ON users (email) WHERE active = true;

然后插入时使用：

INSERT INTO users (email, active, name)
VALUES ('hi@example.com', true, '张三')
ON CONFLICT (email) WHERE active = true DO NOTHING;

这样只有当插入的 active 为 true 且邮箱冲突时才会触发忽略操作。

使用约束名

当不希望暴露具体列名，或冲突目标由多个列组成的复合唯一约束时，可以直接使用约束名：

CREATE TABLE user_logins (
    user_id INT,
    login_date DATE,
    ip_address INET,
    PRIMARY KEY (user_id, login_date)
);

INSERT INTO user_logins (user_id, login_date, ip_address)
VALUES (1, CURRENT_DATE, '192.168.1.100')
ON CONFLICT ON CONSTRAINT user_logins_pkey DO NOTHING;

带 WHERE 条件的 DO UPDATE

可以在 DO UPDATE 后添加 WHERE 子句，仅在满足条件时才执行更新，否则变为 DO NOTHING 效果。

INSERT INTO inventory (warehouse_id, product_id, quantity)
VALUES (1, 100, 50)
ON CONFLICT (warehouse_id, product_id) DO UPDATE SET
    quantity = inventory.quantity + EXCLUDED.quantity
WHERE inventory.quantity < 100;  -- 只有库存小于100时才累加

常见问题与最佳实践

必须存在冲突目标：不能省略 ON CONFLICT 后的冲突指定，除非表没有任何唯一约束（极少使用）。
多列主键/唯一约束：冲突目标是整个约束的所有列，如 ON CONFLICT (user_id, login_date)。
自增列与 UPSERT：如果表有 SERIAL 主键，即使执行 DO UPDATE，序列值依然会被消耗（因为 PostgreSQL 需要先生成可能的值）。若不希望浪费 ID，可考虑使用其他唯一键作为冲突目标。
视图不可直接使用：只能在基表上执行 ON CONFLICT，视图上的 INSERT 需通过可更新视图并定义合适的冲突处理规则。
权限要求：执行语句需要对表有 INSERT 权限，当使用 DO UPDATE 时，还需要 UPDATE 权限。

与其他数据库的对比

MySQL：使用 INSERT ... ON DUPLICATE KEY UPDATE，语法略有不同，且不支持部分索引冲突。
SQLite：支持 INSERT OR REPLACE 和 INSERT OR IGNORE，但行为与 PostgreSQL 的 ON CONFLICT 不完全一致（REPLACE 会删除再插入）。
PostgreSQL 独有优势：灵活指定冲突目标（包括部分索引、表达式）、支持 EXCLUDED 引用新值、原子性保证。

实战练习

场景：设计用户积分表，每天首次登录奖励 10 积分，之后登录不重复奖励。

CREATE TABLE daily_rewards (
    user_id INT,
    reward_date DATE DEFAULT CURRENT_DATE,
    points INT DEFAULT 0,
    PRIMARY KEY (user_id, reward_date)
);

-- 首次登录或当天首次奖励
INSERT INTO daily_rewards (user_id, points)
VALUES (42, 10)
ON CONFLICT ON CONSTRAINT daily_rewards_pkey DO NOTHING;