From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Christophe Labouisse <labouiss@cybercable.fr>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Index on date_trunc
Date: Mon, 3 May 1999 12:42:05 +0300
Message-ID: <l03130300b3531e24aa9a@[147.233.159.109]> (raw)
In-Reply-To: <m3n1zqadne.fsf@gabuzo.meunet>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, labouiss@cybercable.fr
Subject: Re: [SQL] Index on date_trunc
In-Reply-To: <l03130300b3531e24aa9a@[147.233.159.109]>
References: <m3n1zqadne.fsf@gabuzo.meunet> <l03130300b3531e24aa9a@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox