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

On 14 October 2011 00:49, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
> On 10/13/2011 04:32 PM, Tom Lane wrote:
>>
>> 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...
>>
>> timestamptz + interval is not immutable because the results can vary
>> depending on timezone.  For instance, in my zone (America/New_York):
>>
> So it seems like a potential workaround, depending on the nature of your
> data and applications, would be to convert the timestamptz into a timestamp
> at a reference TZ:
>
> steve=# create table testfoo (a_timestamptz timestamptz, an_interval
> interval);
> CREATE TABLE
> steve=# create index testfoo_index on testfoo ((a_timestamptz at time zone
> 'UTC' + an_interval));
> CREATE INDEX
>
> You will have to be sure you are getting the results you want in the
> vicinity of DST changes and if you are handling multiple timezones.
>
> Cheers,
> Steve
>
>

Thanks all

That makes a lot of sense. For some reason I'd thought that having
the timezone would make it immutable (since it represents an absolute
point in time) whereas without it would not be (since the point in
time it *actually* represents is dependant on time zone...). Guess I
hadn't thought that through very well.

Kudos to Postgres for pointing out a flaw in my design! I'll be adding
in the timezone to the table (or at least a table it references).

The new index looks more like this:
create index foo_next_update on foo ( ((first_update + (update_cycle *
update_count)) at time zone update_region) )

I'm not sure timezone will ever be anything but 'GB' in this case, but
there's nothing like future proofing.

Regards All