On Aug 22, 2006, at 5:35 PM, Tom Lane wrote:
"Arturo Perez" <[EMAIL PROTECTED]> 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
Ah, I knew it was something I was overlooking. Thanks a ton. We need
sub-day granularity (it's for a sort of weblog). Without a TZ sounds
llke
a winner.
Thanks again,
arturo
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
