Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1FG0CE43390 for ; Fri, 15 Feb 2002 11:00:12 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g1FG0Bf14113; Fri, 15 Feb 2002 11:00:11 -0500 (EST) To: nickf@ontko.com cc: "PGSQL-SQL" Subject: Re: creating an index on a function In-reply-to: References: Comments: In-reply-to "Nick Fankhauser" message dated "Fri, 15 Feb 2002 09:26:25 -0500" Date: Fri, 15 Feb 2002 11:00:11 -0500 Message-ID: <14110.1013788811@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200202/175 X-Sequence-Number: 6601 "Nick Fankhauser" writes: > staging=# create index event_day on > event(date_trunc('day',event_date_time)); > ERROR: parser: parse error at or near "'" You missed the fine print that says the function must be applied to table column name(s) only. No constants, no expressions. You can get around this limitation by defining a custom function that fills in whatever extra baggage you need. My own first thought was that you could just use conversion to type date, but that falls down. Not for syntax reasons though: regression=# create table foo (event_date_time timestamp); CREATE regression=# create index event_day on foo (date(event_date_time)); ERROR: DefineIndex: index function must be marked iscachable This raises a subtle point that you'd better think about before you go too far in this direction: truncating a timestamp to date is not a very well-defined operation, because it depends on the timezone setting. Indexes on functions whose values might vary depend on who's executing them are a recipe for disaster --- the index is almost certainly going to wind up corrupted (out of order). regards, tom lane