Re: Constraint exclusion with box and integer

On Fri, 2011-01-21 at 18:36 +0000, Daniele Varrazzo wrote:
> => alter table commission_rate add constraint check_overlapping
> exclude using gist (payer_id with =, box( point(extract(epoch from
> start_date), extract(epoch from start_date)),
> point(coalesce(extract(epoch from end_date), 'infinity'),
> coalesce(extract(epoch from end_date),'infinity') ) ) with &&);
> ERROR: data type integer has no default operator class for access method "gist"
> HINT: You must specify an operator class for the index or define a
> default operator class for the data type.
>
> Can I build a constraint check using both a box (for the range) and an
> integer (for a fkey) or should I go back to the classic trigger + lock
> solution?

Install btree_gist, and this should work.

Exclusion constraints are enforced with an index, and an index can only
have one index access method (btree, gist, etc.). So you need to have
one index access method that works for both "=" on integers and "&&" on
boxes.

There's no hope of making a btree work for "&&" on boxes, so we need to
make gist work for "=" on integers.

Regards,
Jeff Davis