Re: Trouble with pg_dumpall import with 7.2

Couldn't you simply index on the cast of the timestamp to date?

create index ix_test on testtable (cast(things as date));
ERROR: parser: parse error at or near "cast"

Evidently not...
--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Masaru Sugawara" <rk73(at)echna(dot)ne(dot)jp>
To: <herve(at)elma(dot)fr>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Wednesday, February 20, 2002 12:02 PM
Subject: Re: [HACKERS] Trouble with pg_dumpall import with 7.2

> On Wed, 20 Feb 2002 11:20:36 +0100
> Herv
Piedvache <herve(at)elma(dot)fr> wrote:
>
> > OK it's a bug of postgreSQL 7.2 ??
> >
> > I can apply an index on the field datelog where this field is a
> > timestamp like :
> >
> > create index ix_datelog_date on datelog (date(datelog);
> >
> > ERROR: DefineIndex: index function must be marked iscachable
> >
> > Or could you explain me how to set date() iscachable ?
>
>
> Um, date() function... That sounds like an unavoidable error.
>
> Recently Brent has replied to this sort of subjects on the mailing
list
> of sql, and Tom has implied to us that unexpected results might be
caused
> by depending on the timezone setting. I would think that you're
able to
> create an index easily like (2), but need to inspect the results
cautiously.
>
>
> (1)create function mydate(timestamp) returns date as '
> select date($1);
> ' language 'sql' with (iscachable);
>
> (2)create index ix_datelog_date on datelog(mydate(datelog));
>
> (3)e.g.:
> select count(*) from datelog
> where mydate(datelog) >= '2002-2-1' and mydate(datelog) <=
'2002-2-5' ;
>
> instead of:
> select count(*) from datelog
> where date(datelog) >= '2002-2-1' and date(datelog) <= '2002-2-5'
;
>
>
>
>
> >On Fri, 15 Feb 2002 11:00:11 -0500
> >Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > "Nick Fankhauser" <nickf(at)ontko(dot)com> writes:
> > > staging=# create index event_day on
> > > event(date_trunc('day',event_date_time));
> > > ERROR: parser: parse error at or near "'"
> >
> > You missed the fine print that says the function must be applied
to
> > table column name(s) only. No constants, no expressions.
> >
> > You can get around this limitation by defining a custom function
that
> > fills in whatever extra baggage you need.
> >
> > My own first thought was that you could just use conversion to
type
> > date, but that falls down. Not for syntax reasons though:
> >
> > regression=# create table foo (event_date_time timestamp);
> > CREATE
> > regression=# create index event_day on foo
(date(event_date_time));
> > ERROR: DefineIndex: index function must be marked iscachable
> >
> > This raises a subtle point that you'd better think about before
you go
> > too far in this direction: truncating a timestamp to date is not a
very
> > well-defined operation, because it depends on the timezone
setting.
> > Indexes on functions whose values might vary depend on who's
executing
> > them are a recipe for disaster --- the index is almost certainly
going
> > to wind up corrupted (out of order).
> >
> > regards, tom lane
>
>
> Regards,
> Masaru Sugawara
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>