Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?

On Tue, 4 May 1999, Thomas Lockhart wrote:

> Date: Tue, 04 May 1999 13:12:07 +0000
> From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
> To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> Cc: Postgres Hackers List <hackers(at)postgreSQL(dot)org>
> Subject: Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ?
>
> > > > btw, how I can find 'something' older than a month
> > > select * from titles
> > > where adate::date < 'today'::Date - '1 month'::timespan;
> > this problem doesn't works:
> > apod=> select * from titles
> > apod-> where adate::date < 'today'::Date - '1 month'::timespan;
> > ERROR: There is more than one possible operator '<'
> > for types 'date' and 'datetime'
> > but if I specify Datetime instead of Date it works, but still doesn't
> > use index.
> > apod=> explain select * from titles
> > apod-> where adate::datetime < 'today'::Datetime
> > apod-> - '1 month'::timespan;
> > NOTICE: QUERY PLAN:
> > Seq Scan on titles (cost=64.10 size=466 width=28)
>
> OK, try
>
> select * from titles
> where adate < date('today'::Datetime - '1 month'::timespan);
>
> although there may (still) be problems with Postgres recognizing that
> it could use an index when the "constant" is an expression.
>
> Let us know what you find out...

No, it's doing Seq Scan. I checked with 6.4.2 and current 6.5 cvs

Oleg

>
> - Tom
>
> --
> Thomas Lockhart lockhart(at)alumni(dot)caltech(dot)edu
> South Pasadena, California
>

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83