Re: Exclusion constraint issue

On Fri, Sep 24, 2010 at 3:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Eric McKeeth <eldin00(at)gmail(dot)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?