PostgreSQL 无法将 ON CONFLICT DO UPDATE 用作通用的“匹配任一唯一键”操作。选择一个定义身份的键，或者在事务中显式检查两个键，并拒绝同时指向不同两行的输入。

本教程帮助在 Mac 终端中工作的开发者使用 node-postgres 实现第二种策略：两个键都不匹配时插入，恰好匹配一篇文章时更新，如果两个键指向不同文章，则明确返回冲突。所有行均为合成数据。

SQL 示例以 PostgreSQL 18 为目标版本，这是检索到的当前文档所对应的版本。实现使用一张普通的非分区表，并设置两个立即生效的唯一约束。根据其他版本的手册改编示例前，请先检查服务器的主版本号。PostgreSQL 18 INSERT 文档

为什么两个唯一约束不能合并成一个冲突目标

假设一篇文章有一个外部引用和一个 slug，两者都必须各自唯一。在一个空的演示数据库中，先运行以下完整设置，并将其保存为 setup.sql。

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

这些声明分别强制执行两条规则。如果将它们替换为 UNIQUE (external_key, slug)，数据模型就会改变：只有这两个字段的组合必须唯一，因此不同组合中可以重复外部键，也可以重复 slug。让两列都保持 NOT NULL，也可以避免在本示例中引入可空键的行为。PostgreSQL 约束

对于 DO UPDATE，PostgreSQL 要求指定冲突目标。列列表描述匹配唯一索引所包含的列；它并不表示“任一列”。因此，ON CONFLICT (external_key, slug) 无法从上述两个独立约束中推断出仲裁索引。指定约束名称只会选中该约束，而且语法只提供一个 ON CONFLICT 子句，并不支持串联多个备选处理程序。PostgreSQL INSERT：ON CONFLICT 子句

更深层的问题是要判断传入行代表哪篇现有文章。考虑传入值 feed-101 和 green-notes：外部键属于蓝色文章，而 slug 属于绿色文章。如果用这两个传入键更新任一行，就会与另一行冲突。选择第一个匹配项会掩盖尚未解决的输入决策。

确定每种匹配情况应如何处理

编写查询前，请先使用以下决策表。这是根据此架构提出的应用策略，并非 PostgreSQL 自动提供的行为。

传入的外部键 传入的 slug 匹配到的不同记录数 本教程中的操作
未使用 未使用 0 插入一篇文章
匹配一篇文章 未使用 1 更新该文章，包括其 slug
未使用 匹配一篇文章 1 更新该文章，包括其外部键
匹配文章 A 匹配文章 A 1 更新文章 A
匹配文章 A 匹配文章 B 2 拒绝输入；保留两篇文章

在只有一个匹配项的情况下，策略有意允许任一键识别文章，并允许更改另一个键。请确认这符合你的导入需求。如果外部键绝不能更改，那么仅匹配到 slug 时应改为应用层冲突。

如果你的实际规则更简单——“外部键始终决定文章身份”——请使用常规的指定冲突目标 upsert。下面这条完整语句可在运行上述设置后执行：

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

这里的 EXCLUDED 提供了拟写入的值。另一个唯一约束仍然有效：如果将拟写入的 slug 改为 green-notes，就会产生独立的唯一性冲突，而不会改为更新另一篇文章。指定冲突目标的 upsert 仅在不存在其他独立错误时，才保证原子地插入或更新。PostgreSQL INSERT 文档

在并发写入期间保持任一键决策的稳定性

先查询再插入，会在做出判断和写入之间产生间隙。另一笔事务可能在此间隙中插入匹配的键。查询中添加 FOR UPDATE 可以锁定检索到的行；如果没有匹配行，就没有检索到的行可锁。因此，仅靠行锁不足以处理这种零匹配情况。PostgreSQL 显式锁定

对于小型导入或偶尔执行的桌面操作，一个直接的方案是在检查任一键之前，对表加 SHARE ROW EXCLUSIVE 锁。该模式会与普通插入、更新和删除操作获取的 ROW EXCLUSIVE 锁冲突，也会与另一个相同模式的锁冲突。普通读取仍可并行进行。PostgreSQL 表级锁模式

最终的执行顺序是：开始事务、获取表锁、查找不同的匹配行、根据决策表处理，然后提交。显式锁会等待与之冲突的锁，并一直持有到事务结束。PostgreSQL LOCK

此处请显式使用 READ COMMITTED。查询会看到在该查询开始前已提交的数据，包括等待获取锁期间完成的写入。将这一快照行为与已获取的表锁结合起来，可为本实现的判断和写入提供稳定依据。PostgreSQL 事务隔离

这是一个范围较广的锁：不相关的文章写入也会等待。请缩短事务，在加锁到提交之间不要进行交互式提示或外部操作。对于频繁的并发导入，在采用此设计前，请重新考虑是否可以用一个规范键来定义身份。

使用 node-postgres 实现事务

你需要一个正在运行的 PostgreSQL 18 演示数据库、psql 命令，以及与你安装的 node-postgres 软件包兼容的 Node.js 版本。在终端中安装软件包并加载设置文件。第一个命令会在演示目录尚无软件包元数据时创建这些元数据。node-postgres 安装与兼容性

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

这些命令假设你当前的本地连接设置已选择演示数据库。程序使用 new Pool()，它读取与 psql 相同的 PostgreSQL 连接环境变量；如有需要，将 PGDATABASE 设置为演示数据库名称。node-postgres 连接

将以下完整程序保存为 upsert-articles.mjs。程序假设设置表没有会改变匹配或更新行为的其他触发器或规则。值通过与 SQL 文本分开提供的位置参数传入。node-postgres 查询

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

根据 node-postgres 的要求，每条事务语句都使用同一个已获取的客户端。通过不同的 pool.query() 调用发送这些语句，无法满足这一连接要求。node-postgres 事务

finally 代码块始终会释放已获取的客户端。如果回滚本身失败，release(true) 会丢弃该连接。桌面脚本结束时，外层的 pool.end() 会关闭连接池。node-postgres 连接池 API

运行程序：node upsert-articles.mjs。预期结果是蓝色文章的标题更新，而键保持不变。JavaScript 语法已通过 Node.js 检查；数据库示例已对照文档审阅，但未在 PostgreSQL 服务器上实际执行。

检查各个分支并显式处理失败

使用设置中的行，修改传给 upsertArticle() 的对象，以测试以下情况。每种情况都应针对原始设置状态运行，或考虑之前测试造成的更改。

externalKey slug 预期结果
feed-303 amber-notes 插入一篇新文章
feed-101 blue-revised 更新蓝色文章并替换其 slug
feed-303 blue-notes 更新蓝色文章并替换其外部键
feed-101 blue-notes 更新蓝色文章的标题
feed-101 green-notes 抛出 ARTICLE_KEY_CONFLICT；两篇文章都不更改

查询返回的是行，因此同时满足两个条件的文章只计数一次。对于这两个独立且非空的唯一键，最多有两行不同的记录可以匹配。代码绝不会通过使用 LIMIT 1、删除文章或静默跳过输入来处理两行结果。

ARTICLE_KEY_CONFLICT 是应用自定义代码。PostgreSQL 的 23505 表示 unique_violation；它是另一种独立情况。改编示例后如果出现后者，请检查违反了哪个约束以及是否增加了任何表行为，不要把所有重复情况都视为相同结果。PostgreSQL 建议检查错误代码，而不是解析错误消息文本。PostgreSQL 错误代码

获取锁可能需要等待。如果希望此桌面任务立即失败，可以将加锁语句替换为 LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT。如果无法立即获取锁，PostgreSQL 会报错，程序随后执行回滚流程。PostgreSQL LOCK

DO NOTHING 或 MERGE 能简化任务吗？

不指定冲突目标的 ON CONFLICT DO NOTHING 可以跳过与任一可用唯一约束或索引冲突的插入。只有在预期行为就是跳过时才选择它；它无法实现此处制定的更新策略。PostgreSQL INSERT 文档

MERGE 在 PostgreSQL 15 中引入。虽然它允许联接条件涉及任一键，但如果源行匹配到两篇不同的目标文章，该联接仍需有相应的处理策略。其操作仍受普通约束限制，并且其并发行为不能与指定冲突目标的 upsert 互换。PostgreSQL 15 发行说明、PostgreSQL MERGE

对于任一键匹配的任务，应明确根据匹配数量做出决策：零个则插入，一个则更新，两个则停止并返回有用的冲突信息。保留唯一约束，并选择一种并发策略，使这一决策在写入完成前保持有效。如果可以接受对文章写入进行串行化，表锁实现可作为一个具体的起点。

来源