Обсуждение: type cast in index

Hello,
    i have reading in the mailing list any messages where different people use this
format to establish a functional index in a column using a type cast.

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

I have tried with the typical CAST too but i get the same error, the column in
the table is:

                Column          |           Type                        |
Modifiers
time_stamp_creacion     | timestamp with time zone | default now()

What can be the problem? Thanks.

Regards,
Miguel Angel.

>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ó:
>> 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.
>