From: Tom Lane <tgl@sss.pgh.pa.us>
To: Herouth Maoz <herouth@oumail.openu.ac.il>
Cc: Christophe Labouisse <labouiss@cybercable.fr>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Index on date_trunc
Date: Mon, 03 May 1999 09:52:54 -0400
Message-ID: <19798.925739574@sss.pgh.pa.us> (raw)
In-Reply-To: <l03130300b3531e24aa9a@[147.233.159.109]>
Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> 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);
> Seems as if the syntax requires that all the arguments for the function
> should be attributes.
Yes, I believe that's the case. It's not only the parser that's
limited, either: the physical representation of indices doesn't
currently have room for anything more than a function OID to define
what the index sort function is. So there's noplace to put a constant
value.
> Solution: create a function that hides the 'day' in it:
Good thought, but it doesn't actually work, as you'll find as soon
as there are any entries in the table:
insert into test values ('1999-08-01 10:15');
ERROR: SQL-language function not supported in this context.
(6.4.x gives a misleading error message, but it's the same restriction.)
It'd be nice to allow SQL functions to be used for indexes, but I'm not
sure what it'd take to make it happen. At the very least there'd have
to be some drastic restrictions on what the function could do (imagine
the carnage if the function tries to modify the table the index is
being built for...)
Bottom line is there's no easy way to do this right now :-(. What I'd
suggest is keeping a separate column that is the day part of the date
and indexing that. You could use a rule to update that column
automatically whenever the main timestamp column is set. Alternatively,
try to restructure your queries so that you don't actually need an index
on the day part of the date...
regards, tom lane
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: tgl@sss.pgh.pa.us, herouth@oumail.openu.ac.il, labouiss@cybercable.fr
Subject: Re: [SQL] Index on date_trunc
In-Reply-To: <19798.925739574@sss.pgh.pa.us>
References: <l03130300b3531e24aa9a@[147.233.159.109]> <19798.925739574@sss.pgh.pa.us>
* 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