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