Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g08ARxB32629 for ; Tue, 8 Jan 2002 05:28:05 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g08AAXs54362 for pgsql-sql@postgresql.org; Tue, 8 Jan 2002 05:10:33 -0500 (EST) (envelope-from news) From: "Steve Brett" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Error trying to create a functional index. Date: Tue, 8 Jan 2002 09:33:31 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 83 Message-ID: References: <3C397FF1.20706@mportal.hu> X-Complaints-To: news@hub.org X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-sql@postgresql.org X-Archive-Number: 200201/121 X-Sequence-Number: 6210 i stand totally corrected. :-) Steve "CoL" wrote in message news:3C397FF1.20706@mportal.hu... > 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 > >> > > > > >