Re: Interval constant syntax, was Re: Interval & check

On Mon, 29 Mar 2004, Karl O. Pinc wrote:

>
> On 2004.03.29 14:44 Bruno Wolff III wrote:
> >
> > In postgres you shouldn't have to explicitly cast the constant to an
> > interval as long as there isn't one than one >= operator that could
> > be applied (depending on the eventaul type of the constant). I would
> > really be surprized if this were to happen for >= and an interval
> > operand
> > on one side or the other.
> >
> > It won't work with two unknown constants, if that was what you tested.
> > Try just casting on one side.
>
> This is my plpgsql code
>
>   PERFORM MATUREDATES.sname FROM MATUREDATES
>      WHERE NEW.sname = MATUREDATES.sname
>            AND ( NEW.birth > MATUREDATES.Matured - ''3 years''
>                  OR NEW.birth > MATUREDATES.Matured - ''7 years'');
>    IF FOUND THEN
>
> And this was my scratch psql test:
>
> => select CAST('1/1/2004' AS date) - '3 years';
> ERROR:  Bad date external representation '3 years'

I think that's because (date - date) is the prefered interpretation.

The best way to specify an interval literal is probably
 INTERVAL '3 years'
which is close to the SQL specification of an interval literal.