Re: Error: timestamp with timezone + interval is not immutable while creating index

Phil Couling <couling(at)gmail(dot)com> writes:
> main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ;
> ERROR: functions in index expression must be marked IMMUTABLE

> Does anyone know why adding two fields like this results in anything
> other than an immutable function? Under what circumstances could it
> return a different result?

timestamptz + interval is not immutable because the results can vary
depending on timezone. For instance, in my zone (America/New_York):

regression=# select '2011-11-06 00:00'::timestamptz;
timestamptz
------------------------
2011-11-06 00:00:00-04
(1 row)

regression=# select '2011-11-06 00:00'::timestamptz + '1 day'::interval;
?column?
------------------------
2011-11-07 00:00:00-05
(1 row)

regression=# select '2011-11-06 00:00'::timestamptz + '24 hours'::interval;
?column?
------------------------
2011-11-06 23:00:00-05
(1 row)

In a zone where that day wasn't a DST transition day, you'd get
different results. IOW, adding '1 day' can mean 23, 24, or 25 hours
depending on your zone and the particular date.

If the interval value doesn't contain any days or months or larger
components, then yeah, the sum would be immutable ... but unfortunately
there's no way to express that concept given the particular datatypes
we've got.

regards, tom lane