Re: Trouble with pg_dumpall import with 7.2 - Mailing list pgsql-hackers

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@echna.ne.jp>
To: <herve@elma.fr>
Cc: <pgsql-hackers@postgresql.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@elma.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@sss.pgh.pa.us> wrote:
> >
> > "Nick Fankhauser" <nickf@ontko.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
>