Postgresql EXCLUDE constraint not triggering ON CONFLICT when INSERT

Question

I have defined this table:

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 &&)
);

But when I'm doing this 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);

I'm expecting the ON CONFLICT clause to be executed, given that there is some row to be inserted having the same item_id, sale_price and a valid_time adjacent to the existent one. Instead I'm getting this:

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

Do I misunderstand something? I believe the exclusion constraint in the second EXCLUDE clause involves the same 3 columns. I'm searching the docs but the ON CONFLICT instructions are not that clear to me.

Scoville

Answer 1

The documentation somewhat tersely remarks:

Note that exclusion constraints are not supported as arbiters with ON CONFLICT DO UPDATE.

Looking at the source code makes the case clearer:

• 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.

Zestful