date_trunc'd timestamp index possible?

From: "D(dot) Duccini" <duccini(at)backpack(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Cc: Pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: date_trunc'd timestamp index possible?
Date: 2004-09-28 00:14:09
Message-ID: Pine.GSO.4.10.10409271910480.22691-100000@solarwind
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-novice pgsql-sql


I'm trying to create a index from a timestamp+tz field and want the index
to be date_trunc'd down to just the date

when i try to do a

create idxfoo on foo (date(footime));

i get a

ERROR: DefineIndex: index function must be marked IMMUTABLE

and it chokes on when i try to use the date_trunc() function as well

create idxfoo on foo (date_trunc('day',footime));

ERROR: parser: parse error at or near "'day'" at character 53

Any suggestions/workarounds (other than creating additional date-only
columns in the schema and indexing those???)

-d

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Serguei A. Mokhov 2004-09-28 05:51:06 Re: Compiling from CVS: undefined reference to
Previous Message Tom Lane 2004-09-28 00:13:16 Re: [HACKERS] Win32 Version numbering patch

Browse pgsql-novice by date

  From Date Subject
Next Message Kumar S 2004-09-28 01:51:14 NOTICE: adding missing FROM-clause entry for table
Previous Message Gaetano Mendola 2004-09-27 23:17:49 Re: Using PERFORM in plpgsql throws compile errors

Browse pgsql-sql by date

  From Date Subject
Next Message sreejith s 2004-09-28 03:27:50 Postgres Doubt
Previous Message vab ip 2004-09-27 16:34:40 Error : could not send data to client