Re: type cast in index

"Chris Spotts" <rfusca(at)gmail(dot)com> writes:
>> CREATE INDEX fecha_creacion_ordtrab ON orden_trabajo_cabecera USING btree
>> ((time_stamp_creacion::date));
>>
>> but in my postgresql 8.3 version i get this error:
>>
>> ERROR: functions in index expression must be marked IMMUTABLE

> If your time_stamp_creacion is a timestamp with time zone I
> believe that the cast is not immutable.

Precisely, because it depends on the value of the TimeZone setting.

I believe that you could build an index on an expression along the lines
of
(time_stamp_creacion AT TIME ZONE 'UTC')::date
or whatever other time zone you wanted to specify. Whether this'd be
very useful is another question ... I'm afraid you'd have to write the
exact same expression in any query you hoped would use the index.

regards, tom lane