Re: [8.1.4] Create index on timestamp fails

"Arturo Perez" <aperez@hayesinc.com> writes:
> I have a table with an column:
>     entry_date | timestamp with time zone| not null

> And when I try to create an index on it like so:
>     create index entry_date_idx on =
> user_tracking(date_part('year',entry_date));

> I get a
>     ERROR: functions in index expression must be marked IMMUTABLE

> According to the mailing lists, this has been working since 7.4.

I seriously doubt that.  date_part on a timestamptz is stable, not
immutable, and AFAICT has been marked that way since 7.3.  The problem
is that the results depend on your current TimeZone setting --- for
instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live.

If you only need day precision, try storing entry_date as a date instead
of a timestamptz.  Or perhaps consider timestamp without tz.  But you
need something that's not timezone-dependent to make this work.

            regards, tom lane