Hello, I have the typical problem of overlapping periods exclusion and I'm trying to give constraint exclusion a go as we have recently switched to a PG 9.0 server.
All the examples I've found check a record against the whole table. But in practice I will have a table of resources (hotel rooms, whatever) and independent periods for each resource. EXCLUDE allows a list of "field WITH op" clauses, and I've checked that they are ANDed so the record is rejected only in case all the checks return true, but I don't see how to combine a check on a range with a check on the resource id: => alter table commission_rate add constraint check_overlapping exclude (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 box has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. => 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? Thanks -- Daniele -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
