Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g08BReB41161 for ; Tue, 8 Jan 2002 06:27:46 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g08BRUa55157 for pgsql-sql@postgresql.org; Tue, 8 Jan 2002 06:27:30 -0500 (EST) (envelope-from news) From: Jochem van Dieten X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Error trying to create a functional index. Date: Tue, 08 Jan 2002 12:27:28 +0100 Organization: OnLine Internet Lines: 27 Message-ID: <3C3AD7A0.9090304@dev.null> References: <3C397FF1.20706@mportal.hu> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.tudelft.nl User-Agent: Mozilla/5.0 (Windows; U; WinNT4.0; en-US; rv:0.9.7+) Gecko/20020107 X-Accept-Language: en-us To: pgsql-sql@postgresql.org X-Archive-Number: 200201/136 X-Sequence-Number: 6224 CoL wrote: > Steve: of course you can make functional indices: > > So why this not workin???? (not workin for me 2 too, pg 7.1.3) > "CREATE INDEX eventdateindex ON event (date_trunc('day', eventstamp));" > > ---------------- > 7.5. Functional Indices > For a functional index, an index is defined on the result of a function > applied to one or more columns of a single table. Functional indices can > be used to obtain fast access to data based on the result of function > calls. > For example, a common way to do case-insensitive comparisons is to use > the lower: > SELECT * FROM test1 WHERE lower(col1) = 'value'; > In order for that query to be able to use an index, it has to be defined > on the result of the lower(column) operation: > CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1)); > The function in the index definition can take more than one argument, > but they must be table columns, not constants. Wouldn't your problem be that 'day' is a constant? Jochem