On Fri, Jan 21, 2011 at 9:11 PM, Jeff Davis <pgsql@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
