X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8F3B8329E54;
	Tue, 28 Sep 2004 01:15:35 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00832-04; Tue, 28 Sep 2004 00:15:31 +0000 (GMT)
Received: from solarwind.bpsi.net (solarwind.bpsi.net [209.54.240.1])
	by svr1.postgresql.org (Postfix) with ESMTP id 854B0329FDD;
	Tue, 28 Sep 2004 01:15:32 +0100 (BST)
Received: from ra.bpsi.net (ra.bpsi.net [199.199.134.1])
	by solarwind.bpsi.net (8.12.8/8.12.8) with ESMTP id i8S0E9MD023653;
	Mon, 27 Sep 2004 19:14:09 -0500 (CDT)
Date: Mon, 27 Sep 2004 19:14:09 -0500 (CDT)
From: "D. Duccini" <duccini@backpack.com>
X-Sender: duccini@solarwind
To: pgsql-sql@postgresql.org
Cc: Pgsql-novice <pgsql-novice@postgresql.org>
Subject: date_trunc'd timestamp index possible?
In-Reply-To: <web-1583353@davinci.ethosmedia.com>
Message-ID: <Pine.GSO.4.10.10409271910480.22691-100000@solarwind>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/4
X-Sequence-Number: 11089


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