PostgreSQL ON CONFLICT报错：无匹配唯一/排除约束问题排查

在x86_64平台的PostgreSQL 16.8执行以下INSERT语句时，报错there is no unique or exclusion constraint matching the ON CONFLICT specification：

INSERT INTO supplier_expedia_amenity_translations (
  name, locale, supplier_expedia_amenity_id, 
  created_at, updated_at
) 
VALUES 
  (
    'Free massage included', 'en', 1723, 
    CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
  ) ON CONFLICT (
    locale, supplier_expedia_amenity_id
  ) DO UPDATE 
SET 
  updated_at =(
    CASE WHEN (
      supplier_expedia_amenity_translations.name IS NOT DISTINCT 
      FROM 
        excluded.name
    ) THEN supplier_expedia_amenity_translations.updated_at ELSE CURRENT_TIMESTAMP END
  ), 
  name = excluded.name RETURNING id

该语句在其他同结构表上可正常执行，多次重建索引无效，但在另一台机器无此问题。

查询表定义发现，冲突列locale和supplier_expedia_amenity_id对应的两个唯一索引均为INVALID状态：

Column            |              Type              | Collation | Nullable |                              Default                               
-----------------------------+--------------------------------+-----------+----------+-------------------------------------------------------------------
 id                          | bigint                         |           | not null | nextval('supplier_expedia_amenity_translations_id_seq'::regclass)
 supplier_expedia_amenity_id | bigint                         |           | not null | 
 locale                      | character varying              |           | not null | 
 created_at                  | timestamp(6) without time zone |           | not null | 
 updated_at                  | timestamp(6) without time zone |           | not null | 
 name                        | character varying              |           |          | 
Indexes:
    "supplier_expedia_amenity_translations_pkey" PRIMARY KEY, btree (id)
    "idx_expedia_amenity_translations_unique" UNIQUE, btree (locale, supplier_expedia_amenity_id) INVALID
    "idx_expedia_amenity_translations_unique_ccnew" UNIQUE, btree (locale, supplier_expedia_amenity_id) INVALID
    "index_29d07c8203c48d59038d6bb9eefb0e7564ea1df2" btree (supplier_expedia_amenity_id)
    "index_supplier_expedia_amenity_translations_on_locale" btree (locale)

通过ActiveRecord查询索引也确认这些唯一索引valid=false。

原因分析 #

PostgreSQL的INSERT...ON CONFLICT依赖**有效（VALID）**的唯一约束/索引来检测冲突。当索引处于INVALID状态时，数据库无法将其用于冲突检测，因此触发报错。

索引INVALID通常由以下情况导致：

• 索引创建/重建过程被中断（如数据库崩溃、连接断开）
• 表数据存在违反唯一约束的重复行，导致索引重建失败
• 系统资源不足导致索引构建未完成

解决方案 #

1. 清理无效索引 #

先删除所有无效的重复唯一索引，避免后续操作冲突：

DROP INDEX IF EXISTS idx_expedia_amenity_translations_unique;
DROP INDEX IF EXISTS idx_expedia_amenity_translations_unique_ccnew;

2. 检查并处理重复数据 #

在创建新索引前，必须确保冲突列组合(locale, supplier_expedia_amenity_id)没有重复行，否则索引创建会失败：

SELECT locale, supplier_expedia_amenity_id, COUNT(*)
FROM supplier_expedia_amenity_translations
GROUP BY locale, supplier_expedia_amenity_id
HAVING COUNT(*) > 1;

如果查询到重复行，需先处理（如删除重复项或合并数据）。

3. 创建有效的唯一索引/约束 #

清理重复数据后，重新创建唯一索引：

CREATE UNIQUE INDEX idx_expedia_amenity_translations_unique
ON supplier_expedia_amenity_translations (locale, supplier_expedia_amenity_id);

或推荐使用唯一约束（约束比独立索引更严谨）：

ALTER TABLE supplier_expedia_amenity_translations
ADD CONSTRAINT idx_expedia_amenity_translations_unique
UNIQUE (locale, supplier_expedia_amenity_id);

4. 验证索引状态 #

创建完成后，查询表定义确认索引状态为VALID：

\d supplier_expedia_amenity_translations

或通过ActiveRecord验证：

ActiveRecord::Base.connection.indexes(:supplier_expedia_amenity_translations).select { |i| i.unique && i.columns == ["locale", "supplier_expedia_amenity_id"] }.first.valid?

5. 重新执行INSERT语句 #

索引验证有效后，再次执行原INSERT...ON CONFLICT语句即可正常运行。

内容的提问来源于stack exchange，提问作者Amit Patel