PostgreSQL带WHERE条件唯一索引的ON CONFLICT DO UPDATE语法求助

你的问题出在使用了带WHERE条件的部分唯一索引，但ON CONFLICT语句没有完整匹配索引的定义。以下是两种正确的写法：

方法1：直接指定唯一索引名称 #

这是最简洁可靠的方式，直接引用你创建的索引名称即可：

INSERT INTO deferred_task (queue, attempt, payload, reschedule_at)
VALUES ('check_payment', 1,
        '{"attempt":1,"transactionId":"6ec0a1c1-9d07-4785-8114-b5232dd83c5c","expectedStatuses":null}'::jsonb,
        '2024-09-05 11:01:10')
ON CONFLICT ON CONSTRAINT idx_deferred_task_unique_payment_check 
DO UPDATE SET reschedule_at = EXCLUDED.reschedule_at;

用EXCLUDED引用插入语句中的值，避免重复硬编码，更安全简洁。

方法2：完整匹配索引的唯一表达式与WHERE条件 #

如果不想依赖索引名称，可以完整复现索引定义中的唯一表达式和过滤条件，让PostgreSQL精准匹配到对应的部分唯一索引：

INSERT INTO deferred_task (queue, attempt, payload, reschedule_at)
VALUES ('check_payment', 1,
        '{"attempt":1,"transactionId":"6ec0a1c1-9d07-4785-8114-b5232dd83c5c","expectedStatuses":null}'::jsonb,
        '2024-09-05 11:01:10')
ON CONFLICT (((payload::json ->> 'transactionId'::text))::uuid)
WHERE queue = ANY (ARRAY['check_payment'::text, 'cash_payment'::text]) AND done_at IS NULL
DO UPDATE SET reschedule_at = EXCLUDED.reschedule_at;

报错原因说明 #

你之前的写法只指定了唯一表达式，但缺少索引的WHERE过滤条件。PostgreSQL的ON CONFLICT需要精确匹配唯一约束/索引的定义——你的索引是部分唯一索引，必须同时提供唯一表达式和对应的过滤条件，或者直接指定索引名称，才能让数据库识别到对应的约束。

内容的提问来源于stack exchange，提问作者Mariusz Jucha