pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (5+ messages)  latest in thread

Message-ID: <l03130300b3531e24aa9a@[147.233.159.109]>
Permalink:  ../l03130300b3531e24aa9a@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130300b3531e24aa9a@[147.233.159.109]

 · 

reply

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