pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex on date_trunc
5+ messages / 4 participants
[nested] [flat]
* Index on date_trunc
@ 1999-04-22 20:21 Christophe Labouisse <christophe.labouisse@dial.oleane.com>
0 siblings, 0 replies; 5+ messages in thread
From: Christophe Labouisse @ 1999-04-22 20:21 UTC (permalink / raw)
To: pgsql-sql
I'm trying to index on a part of a datetime field. I tried the
following statement :
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 ?
--
Christophe Labouisse : Cinéma, typographie, Unix
christophe.labouisse@dial.oleane.com http://www.multimania.com/gabuzo/
Le cinéma en Lumière : http://www.lumiere.org/
^ permalink raw reply [nested|flat] 5+ messages in thread
* [SQL] Index on date_trunc
@ 1999-04-30 05:19 Christophe Labouisse <labouiss@cybercable.fr>
1999-05-03 09:42 ` Re: [SQL] Index on date_trunc Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 5+ messages in thread
From: Christophe Labouisse @ 1999-04-30 05:19 UTC (permalink / raw)
To: pgsql-sql
X-From-Line: owner-pgsql-sql@hub.org Fri Apr 23 06:48:33 1999
Return-Path: <owner-pgsql-sql@hub.org>
Delivered-To: labouiss@cybercable.fr
Received: from mail.cybercable.fr
by localhost with POP3 (fetchmail-5.0.1)
for moi@localhost (single-drop); Fri, 23 Apr 1999 06:48:33 +0200 (CEST)
Received: (qmail 4517 invoked from network); 22 Apr 1999 22:31:12 +0200
Received: from hub.org (209.47.145.100)
by bouvreuil.cybercable.fr with SMTP; 22 Apr 1999 20:31:12 -0000
Received: from localhost (majordom@localhost)
by hub.org (8.9.3/8.9.1) with SMTP id QAA12080;
Thu, 22 Apr 1999 16:30:50 -0400 (EDT)
(envelope-from owner-pgsql-sql@hub.org)
Received: by hub.org (TLB v0.10a (1.23 tibbs 1997/01/09 00:29:32)); Thu, 22 Apr 1999 16:29:27 +0000 (EDT)
Received: (from majordom@localhost)
by hub.org (8.9.3/8.9.1) id QAA11449
for pgsql-sql-outgoing; Thu, 22 Apr 1999 16:29:25 -0400 (EDT)
(envelope-from owner-pgsql-sql@postgreSQL.org)
Received: from gabuzo.meunet (IDENT:root@d224.paris-204.cybercable.fr [212.198.204.224])
by hub.org (8.9.3/8.9.1) with ESMTP id QAA11262
for <pgsql-sql@postgreSQL.org>; Thu, 22 Apr 1999 16:27:09 -0400 (EDT)
(envelope-from christophe.labouisse@dial.oleane.com)
Received: (from moi@localhost)
by gabuzo.meunet (8.9.1a/8.9.1/Christophe Labouisse - 10/01/99) id WAA06056;
Thu, 22 Apr 1999 22:21:50 +0200
Mime-Version: 1.0 (generated by tm-edit 7.108)
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <m3r9pcbe3m.fsf@gabuzo.meunet>
X-Mailer: Gnus v5.5/XEmacs 20.4 - "Emerald"
Sender: owner-pgsql-sql@postgreSQL.org
Precedence: bulk
X-Sorted: ML-PSQL-SQL
Lines: 18
Xref: gabuzo.meunet ML-PSQL-SQL:676
I'm trying to index on a part of a datetime field. I tried the
following statement :
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 ?
Christophe Labouisse : Cinéma, typographie, Unix
christophe.labouisse@dial.oleane.com http://www.multimania.com/gabuzo/
Le cinéma en Lumière : http://www.lumiere.org/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Index on date_trunc
1999-04-30 05:19 [SQL] Index on date_trunc Christophe Labouisse <labouiss@cybercable.fr>
@ 1999-05-03 09:42 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-05-03 13:52 ` Re: [SQL] Index on date_trunc Tom Lane <tgl@sss.pgh.pa.us>
1999-05-03 14:31 ` Re: [SQL] Index on date_trunc Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 2 replies; 5+ messages in thread
From: Herouth Maoz @ 1999-05-03 09:42 UTC (permalink / raw)
To: Christophe Labouisse <labouiss@cybercable.fr>; 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Index on date_trunc
1999-04-30 05:19 [SQL] Index on date_trunc Christophe Labouisse <labouiss@cybercable.fr>
1999-05-03 09:42 ` Re: [SQL] Index on date_trunc Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-05-03 13:52 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-05-03 13:52 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: Christophe Labouisse <labouiss@cybercable.fr>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Index on date_trunc
1999-04-30 05:19 [SQL] Index on date_trunc Christophe Labouisse <labouiss@cybercable.fr>
1999-05-03 09:42 ` Re: [SQL] Index on date_trunc Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-05-03 14:31 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 5+ messages in thread
From: Herouth Maoz @ 1999-05-03 14:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
At 16:52 +0300 on 03/05/1999, Tom Lane wrote:
> 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.
Argh. Chastisement taken. But it really ought to have said that when I
tried creating the index, oughtn't it? :)
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-05-03 14:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-22 20:21 Index on date_trunc Christophe Labouisse <christophe.labouisse@dial.oleane.com>
1999-04-30 05:19 [SQL] Index on date_trunc Christophe Labouisse <labouiss@cybercable.fr>
1999-05-03 09:42 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-05-03 13:52 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-03 14:31 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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