Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g07FveB01647 for ; Mon, 7 Jan 2002 10:57:40 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g07B0KV35653 for pgsql-sql@postgresql.org; Mon, 7 Jan 2002 06:00:20 -0500 (EST) (envelope-from news) From: CoL X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Error trying to create a functional index. Date: Mon, 07 Jan 2002 12:01:05 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 71 Message-ID: <3C397FF1.20706@mportal.hu> References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: news@hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:0.9.7+) Gecko/20020101 X-Accept-Language: en-us To: pgsql-sql@postgresql.org X-Archive-Number: 200201/123 X-Sequence-Number: 6212 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. Functional indices are always single-column (namely, the function result) even if the function uses more than one input field; there cannot be multi-column indices that contain function calls. Tip: The restrictions mentioned in the previous paragraph can easily be worked around by defining custom functions to use in the index definition that call the desired function(s) internally. ---------------- Steve Brett wrote: > i think you need to do this > > CREATE INDEX eventdateindex ON event (eventstamp); > > as i'm 99% certain that you can only create an index on a 'whole' attribute > and not the result of a function. > > Steve > > "Neal Lindsay" wrote in message > news:b01eaea0.0201030725.51661db3@posting.google.com... > >>I have a table called "event": >> Attribute | Type | >>Modifier >>-------------+--------------------------+--------------------------------- >> > ---------------------- > >> eventid | integer | not null default >>nextval('"event_eventid_seq"'::text) >> femployeeid | integer | not null >> ftaskid | integer | not null >> flocaleid | integer | not null >> eventstamp | timestamp with time zone | not null >> duration | smallint | not null >>Index: event_pkey >> >>When I try to create an index: >>CREATE INDEX eventdateindex ON event (date_trunc('day', eventstamp)); >> >>It gives me this error: >>ERROR: parser: parse error at or near "'" >> >>I can "SELECT date_trunc('day', eventstamp) FROM event;", but not >>create an index on that function. What am I doing wrong? >> >>Thanks >>-Neal Lindsay >> > >