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