PostgreSQL EXCLUDE约束在插入时未触发ON CONFLICT。

Question

我已经定义了这个表格：

CREATE TABLE market.price_history (
    item_id bigint NOT NULL,
    valid_time tstzrange DEFAULT tstzrange(now(), 'infinity'),
    sale_price money NOT NULL,
    promo_code bool NOT NULL DEFAULT false,
    EXCLUDE USING gist(item_id WITH =, valid_time WITH &&),
    EXCLUDE USING gist(item_id WITH =, sale_price WITH =, valid_time WITH &&)
);

但是当我执行这个SQL语句时：

WITH new_row_valid_time as(
    SELECT tstzrange(MAX(upper(valid_time)), 
                     'infinity'::timestamptz) as adjacent_valid_time 
    FROM market.price_history 
    WHERE item_id = 11
)
INSERT INTO market.price_history as ph
(item_id, valid_time, sale_price, promo_code)
VALUES(11,                        -- same id as existent row
       (SELECT adjacent_valid_time 
        FROM new_row_valid_time), -- adjacent range for existent row
       11.83,                     -- same price as the existent row
       False)
ON CONFLICT (item_id, sale_price, valid_time) DO
UPDATE SET valid_time = tstzrange(lower(ph.valid_time), 'infinity'::timestamptz)
WHERE ph.item_id = excluded.item_id
    AND ph.sale_price = excluded.sale_price
    AND upper(ph.valid_time) = lower(excluded.valid_time);

我希望执行ON CONFLICT子句，因为要插入的某些行具有相同的item_id、sale_price和与现有行相邻的valid_time。但实际情况是我得到了以下结果：

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

我是否有什么误解？我认为第二个EXCLUDE子句中的排除约束涉及相同的3个列。我正在查找文档，但ON CONFLICT指令对我来说不是很清楚。

- madtyn

Answer 1

文档中简短地提到：

请注意，冲突排除约束不支持作为与 ON CONFLICT DO UPDATE 使用的仲裁者。

查看源代码可以更清楚地理解这一点：

• You can never use an exclusion constraint with ON CONFLICT DO UPDATE.

• You can, however, use

  ON CONFLICT ON CONSTRAINT price_history_item_id_valid_time_excl DO NOTHING
  

  That is, you can use a named exclusion constraint with DO NOTHING.

• There is no “constraint inference” with exclusion constraints, i.e., even in the DO NOTHING case you cannot just specify the indexed expressions in parentheses and have PostgreSQL find the corresponding exclusion constraint.

- Laurenz Albe

---

谢谢！！！！ - atomkirk