From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | Christophe Labouisse <labouiss(at)cybercable(dot)fr>, pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] Index on date_trunc |
Date: | 1999-05-03 09:42:05 |
Message-ID: | l03130300b3531e24aa9a@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | José Soares | 1999-05-03 12:43:28 | Re: [SQL] CASE |
Previous Message | Thomas Malkus | 1999-05-03 08:55:08 | (no subject) |