PostgreSQL 通常允许唯一列中存在多个 SQL NULL 值。要诊断意外的重复键错误，请确定具体索引，检查实际发送的值，并确认其定义是否改变了对 null 的比较方式。唯一索引

本指南适用于使用 Mac 上的 psql 或 node-postgres 调试文章插入操作的用户。指南使用合成的文章行，并提供一套诊断流程：重现默认行为、追踪导致失败的规则，然后选择合适的修正方法。

可为空的唯一列实际上允许什么？

可为空的列可以包含 NULL；唯一约束决定哪些组合可以重复。使用普通的 UNIQUE (slug) 时，重复的非 null slug 会被拒绝，而重复的 null 则允许。使用普通的 UNIQUE (section, slug) 时，只要受约束列中有一列为 null，该约束就允许其他重复组合。表上的其他唯一规则仍然适用。约束

PostgreSQL 15 引入了 NULLS NOT DISTINCT，它会在唯一性检查时将 null 视为相等。对于复合键，整个组合都必须匹配：('notes', NULL) 会与另一个 ('notes', NULL) 冲突，但不会与 ('guides', NULL) 冲突。PostgreSQL 14 不支持该子句。版本 15、唯一索引

实际规则或输入 重复值是否可能导致重复键错误？
普通 UNIQUE (slug)，输入 SQL NULL 不会，仅在此规则下如此
UNIQUE NULLS NOT DISTINCT (slug) 会，在 PostgreSQL 15 或更高版本中
普通 UNIQUE (slug)，输入 '' 会；这是空字符串
普通 UNIQUE (slug)，输入 'NULL' 会；这是文本
同一行上的另一个唯一键 会，与可为空的 slug 无关

SQL NULL、空字符串和文本 'NULL' 是不同的输入。PostgreSQL 的比较文档明确区分 null 和普通文本值；应使用 IS NULL 检查是否为 null。比较函数和运算符

在 Mac 终端中重现默认行为

你需要一个现有的 PostgreSQL 连接，并且 psql 命令可在 Terminal 中使用。使用你平常采用的连接设置打开会话。例如，如果本地数据库名为 postgres，请运行以下命令。-d 选项用于选择数据库。psql

psql -d postgres

在选择特定版本的语法前，先检查所连接服务器的版本。server_version 会报告服务器版本，这是判断是否支持 NULLS NOT DISTINCT 的依据。预设选项

SHOW server_version;

在一个全新的会话中运行一次以下完整基准示例。它使用的普通唯一约束适用于 PostgreSQL 14 及更高版本；该示例只会创建一张临时演示表。

CREATE TEMP TABLE article_probe (
    article_id integer PRIMARY KEY,
    title text NOT NULL,
    slug text,
    CONSTRAINT article_probe_slug_key UNIQUE (slug)
);

INSERT INTO article_probe (article_id, title, slug)
VALUES
    (1, 'Paper shapes', NULL),
    (2, 'Color arrangements', NULL),
    (3, 'Desk sketches', ''),
    (4, 'Pattern notes', 'NULL');

SELECT article_id, title, slug,
       slug IS NULL AS is_sql_null,
       slug = '' AS is_empty_string
FROM article_probe
ORDER BY article_id;

按照显示的定义，四次插入都应成功。前两行的值为 is_sql_null = true；第三行的值为 is_empty_string = true；第四行包含字面文本。涉及 null 的比较本身可能返回 null，因此应使用显式的 null 状态列来解读结果。约束、比较函数和运算符

这个基准示例提供了一个实用的分界点。如果应用程序出错，而此示例成功，请将应用程序实际使用的表定义和提交的值与基准示例进行比较。仅凭列可为空这一点，无法确定是哪条规则导致失败。

找出导致错误的具体规则

先检查 SQLSTATE 23505，这是 PostgreSQL 表示 unique_violation 的代码。应检查代码，而不是匹配错误消息的措辞。然后记录错误报告的 schema、表、约束，以及可用时的详细信息。即使索引是通过 CREATE UNIQUE INDEX 直接创建的，PostgreSQL 仍会将其视为约束，并在错误的约束名称字段中报告索引名。PostgreSQL 错误代码、错误和通知消息字段

在与基准示例相同的 psql 会话中，运行以下诊断查询来检查索引。pg_indexes 视图会显示每个索引的 schema、名称和还原后的定义。修改查询时，请同时匹配错误报告的表和 schema。pg_indexes

SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'article_probe'
ORDER BY schemaname, indexname;

查看所有唯一性定义，先检查错误消息中报告的名称。重复的 article_id 会违反主键，即使 slug 为 null 也是如此。主键要求值唯一且非 null；行中的其他可为空列不会放宽这一要求。约束

检查定义与基准示例之间是否有以下三种变化：

  • null 相等性：NULLS NOT DISTINCT 会有意让重复的 null 键发生冲突。
  • **表达式：**唯一性可能适用于计算后的值，而不是存储的列。
  • **谓词：**部分索引只会对满足其 WHERE 条件的行应用规则。

如果是表达式索引，请在调查冲突时重现该索引表达式。例如，基于 lower(slug) 的索引会拒绝两个仅大小写不同的非 null slug。基于 COALESCE(slug, '') 的索引会将 null 和空字符串映射到相同的索引值，因此这两种输入可能发生冲突。这是表达式索引唯一性和 COALESCE 文档所述行为的结果。表达式索引、条件表达式

部分唯一索引也需要仔细检查。若索引基于 section 且条件为 WHERE slug IS NULL，它会在 slug 为 null 的行中，保证每个 section 只能有一行匹配。它不会对 slug 本身强制唯一性。PostgreSQL 文档说明，部分唯一索引会在谓词选出的子集中强制唯一性。部分索引

检查 node-postgres 实际发送的内容

node-postgres 会将 JavaScript null 和 undefined 查询参数都转换为 SQL null。空字符串和字符串 'NULL' 则仍是普通文本输入。还要区分传入 null 参数与省略 SQL 列：对于省略的列，PostgreSQL 会填入其声明的默认值；如果没有默认值，则填入 null。查询、INSERT

以下完整程序会显示这些差异。程序需要 Node.js、npm，以及你现有的 PostgreSQL 连接设置。在工作目录中安装依赖，将程序保存为 diagnose-null.mjs，然后运行。客户端使用标准的 PostgreSQL 连接环境变量。连接

npm install pg

import pg from 'pg';

const { Client } = pg;
const client = new Client();

async function main() {
  await client.connect();
  try {
    await client.query(`
      CREATE TEMP TABLE article_node_probe (
        title text NOT NULL,
        slug text DEFAULT 'untitled',
        CONSTRAINT article_node_probe_slug_key UNIQUE (slug)
      )
    `);

    const samples = [
      { title: 'Paper shapes', slug: null },
      { title: 'Color arrangements', slug: undefined },
      { title: 'Desk sketches', slug: '' },
      { title: 'More sketches', slug: '' },
      { title: 'Pattern notes', slug: 'NULL' },
      { title: 'More patterns', slug: 'NULL' },
    ];

    for (const sample of samples) {
      console.log('Input:', {
        title: sample.title,
        type: typeof sample.slug,
        value: sample.slug,
      });

      try {
        const result = await client.query(
          `INSERT INTO article_node_probe (title, slug)
           VALUES ($1, $2)
           RETURNING title, slug, slug IS NULL AS is_sql_null`,
          [sample.title, sample.slug],
        );
        console.log('Inserted:', result.rows[0]);
      } catch (error) {
        if (error.code !== '23505') throw error;
        console.log('Duplicate:', {
          code: error.code,
          schema: error.schema,
          table: error.table,
          constraint: error.constraint,
          detail: error.detail,
        });
      }
    }
  } finally {
    await client.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node diagnose-null.mjs

按照此程序中的 schema，前两行应存储 SQL null，尽管已声明默认值。第一个空字符串和第一个 'NULL' 字符串应成功插入；重复插入它们应产生 23505。程序会报告这些预期冲突，并重新抛出其他错误。其诊断属性名称与 node-postgres 的 DatabaseError 字段一致。查询、DatabaseError 源码

准备本文时，我们根据检索到的文档检查了数据库示例，但没有在 PostgreSQL 上实际运行这些示例。

查询可空键时，不要漏掉匹配的行

使用 slug = NULL 进行诊断查询时，将无法找到 null slug。固定搜索 null 时请使用 IS NULL；当值可能为 null 或非 null 时，请使用 IS NOT DISTINCT FROM。后者会在比较时将两个 null 视为相等。比较函数和运算符

以下诊断查询在同一会话中针对前面创建的 article_probe 表运行。查询应返回 slug 为 null 的两行。

SELECT article_id, title, slug
FROM article_probe
WHERE slug IS NOT DISTINCT FROM NULL::text
ORDER BY article_id;

对于参数化查询，相应的谓词片段为 WHERE slug IS NOT DISTINCT FROM $1::text。复合规则需要匹配每个键分量，并重现任何索引表达式或部分索引谓词。找到匹配的 null 行可以确认数据库中存储的内容；这些行是否违反唯一性，仍取决于索引定义。

选择符合预期规则的修正方法

如果文章的 slug 可以任意缺失，普通的 UNIQUE (slug) 已能表达这一要求。请修正意外的文本值、非预期的表达式，或错误消息指出的其他重复键。改变 null 处理方式无法解决其他键上的冲突。

如果要求每个 section 只能有一个 null slug，PostgreSQL 15 及更高版本可以直接表达这一规则。以下独立演示有意以一次预期会产生 23505 的插入结束：第一个行已经占用了该 section 与 null 的组合。版本 15、唯一索引

CREATE TEMP TABLE article_one_missing (
    section text NOT NULL,
    slug text,
    title text NOT NULL,
    CONSTRAINT article_one_missing_key
        UNIQUE NULLS NOT DISTINCT (section, slug)
);

INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_one_missing_key.
INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

在 PostgreSQL 14 上，可针对这个特定的双列设计使用部分唯一索引。以下独立示例实现相同规则，因为 section 为非 null，而 slug 是唯一可为空的键分量。这种做法是根据 PostgreSQL 文档所述的部分索引行为推导得出的。部分索引

CREATE TEMP TABLE article_legacy (
    section text NOT NULL,
    slug text,
    title text NOT NULL
);

CREATE UNIQUE INDEX article_legacy_present_key
ON article_legacy (section, slug)
WHERE slug IS NOT NULL;

CREATE UNIQUE INDEX article_legacy_missing_key
ON article_legacy (section)
WHERE slug IS NULL;

INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_legacy_missing_key.
INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

第一个索引会阻止同一 section 内重复的非空 slug。第二个索引允许每个 section 有一个缺失的 slug。若键中还有其他可空分量，则需要进一步考虑它们可能形成的组合；这两个索引的构造只适用于上述 schema。

最后，请明确选择冲突处理方式。ON CONFLICT DO NOTHING 会跳过冲突插入，但不会修正底层值。ON CONFLICT DO UPDATE 需要一个冲突目标，其中的列、表达式和适用谓词必须能够标识预期规则。这两种操作都不会在普通唯一性规则允许重复 null 时，凭空造成 null 冲突。INSERT

完成诊断的标志是：你能够将错误报告的索引名称对应到其定义，重现提交的键值，并解释这些值为何违反该规则。修正后，请用完全相同的输入再次操作，并检查返回结果和已存储的行，以确认预期文章已得到妥善处理。

来源