From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Constraint exclusion with box and integer |
Date: | 2011-01-21 18:36:53 |
Message-ID: | AANLkTinWsr_cWdKhfcuxtWCsnwJ+XPbB9ArO53P-LWHZ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniele Varrazzo | 2011-01-21 18:48:43 | Re: Constraint exclusion with box and integer |
Previous Message | Robert Haas | 2011-01-21 17:54:53 | Re: [HACKERS] Large object corruption during 'piped' pg_restore |