Обсуждение: Error: timestamp with timezone + interval is not immutable while creating index
Error: timestamp with timezone + interval is not immutable while creating index
От
Phil Couling
Дата:
Hi All I've got a table with (amongst others) two fields: last_updated timestamp with time zone; update_cycle interval; I'd like to create an index on these, to index time "next update" time (last_updated + update_cycle). When I try this I get an error though: main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ; ERROR: functions in index expression must be marked IMMUTABLE Does anyone know why adding two fields like this results in anything other than an immutable function? Under what circumstances could it return a different result? Thanks very much for any help.
Re: Error: timestamp with timezone + interval is not immutable while creating index
От
David Salisbury
Дата:
On 10/13/11 4:38 PM, Phil Couling wrote: > Hi All > > I've got a table with (amongst others) two fields: > last_updated timestamp with time zone; > update_cycle interval; > > I'd like to create an index on these, to index time "next update" time > (last_updated + update_cycle). > > When I try this I get an error though: > > main=> create index foo_next_update on foo( (last_updated + update_cycle) ) ; > ERROR: functions in index expression must be marked IMMUTABLE > > Does anyone know why adding two fields like this results in anything > other than an immutable function? Under what circumstances could it > return a different result? > > Thanks very much for any help. > I believe the problem is that you have a TZ on your timestamp, and that makes things mutable. functions that deal with time can't be marked as immutable do to this reason. -Dave