On Fri, Sep 24, 2010 at 3:22 PM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Eric McKeeth <
eldin00@gmail.com> writes:
> why would I get the following error, since the period() function is in fact
> declared as immutable?
> test=# ALTER TABLE test3 ADD exclude using
> gist(period(effect_date::timestamptz, expire_date::timestamptz) with && );
> ERROR: functions in index expression must be marked IMMUTABLE
period() might be immutable, but those casts from date to timestamptz
are not, because they depend on the TimeZone parameter.
regards, tom lane
Thanks for pointing out what I was overlooking. After a bit of further investigation and testing it seems like the period type I found isn't going to work without modification for my constraint, so I ended up with the following to get the semantics I need:
alter table test3 add exclude using gist(
box(
point(
case when effect_date = '-Infinity'::date
then '-Infinity'::double precision
else date_part('epoch'::text, effect_date)
end,
1
),
point(
case when expire_date = 'Infinity'::date
then 'Infinity'::double precision
else date_part('epoch', expire_date) - 1
end,
1
)
)
with &&
);
This is ugly, but it does seem to enforce the constraint I need, of non-overlapping dates where sharing an endpoint is not considered an overlap. The case blocks are because the date_part bit always returns 0 for infinite dates, which seemed a bit counter-intuitive. Any suggestions on how I could improve on it?