On Fri, Jan 21, 2011 at 9:11 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> 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.
Yes, I see the implementation constraint...
> There's no hope of making a btree work for "&&" on boxes, so we need to
> make gist work for "=" on integers.
Ok, the btree_gist seems exactly the missing link. Will try with that.
Thanks,
-- Daniele