Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA60595 for ; Mon, 3 May 1999 05:41:37 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id B90704B5E6; Mon, 3 May 1999 12:41:30 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA43D9; Mon, 3 May 1999 12:43:42 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 3 May 1999 12:42:05 +0300 To: Christophe Labouisse , pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] Index on date_trunc At 08:19 +0300 on 30/04/1999, Christophe Labouisse wrote: > create index ns_dt1_idx on netstats (date_trunc('day',NS_DATE) datetime_ops); > > It doesn't work and I get : > > ERROR: parser: parse error at or near "'" > > Any idea ? Seems as if the syntax requires that all the arguments for the function should be attributes. That is, columns from the table rather than literals of whatever kind. Solution: create a function that hides the 'day' in it: testing=> \d test Table = test +-----------------------------+----------------------------------+-------+ | Field | Type | Length| +-----------------------------+----------------------------------+-------+ | zman | datetime | 8 | +-----------------------------+----------------------------------+-------+ testing=> create index zman_index on test testing-> (date_trunc( 'day', zman ) datetime_ops ); ERROR: parser: parse error at or near "'" testing=> create function day_trunc( datetime ) returns datetime testing-> as 'SELECT date_trunc( ''day'', $1 )' testing-> language 'sql'; CREATE testing=> create index zman_index on test testing-> (day_trunc( zman ) datetime_ops ); CREATE Just remember to use the same function for the queries you make on the tables. Otherwise PostgreSQL won't use this index, like SELECT * FROM test WHERE day_trunc( zman ) = '1999-08-01'; Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma