type cast in index

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.

Linos escribi�:

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

You can make it work by adding an AT TIME ZONE 'UTC' specification,
which will cause it to be turned into a plain timestamp (without tz).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

"Chris Spotts" <rfusca@gmail.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

Yes it seems you have reason Chris, i have been using 'timestamp with time zone'
without need it because i have read in the mailing list was good practice
because maybe one day you use the database in more timezones but i ever use the
app in the same timezone so i will convert the column datatype where i need an
index in a cast to date from a timestamp. Thanks.

Chris Spotts escribi�: