Re: [HACKERS] adate::Date is equiv. to adate if adate is type of Date ? - Mailing list pgsql-hackers

> > >  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...
                      - Tom

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California