On Fri, Sep 24, 2010 at 3:22 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Eric McKeeth <eldin00@xxxxxxxxx> writes:period() might be immutable, but those casts from date to timestamptz
> 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
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?
