Tom Lane-2 wrote
> Dave Cramer <
> pg@
> > writes:
>> The crux of the problem is this: ERROR: operator does not exist:
>> timestamp
>> with time zone >= interval
>> which appears to be caused by trans_date between now() - $2 and now()
>
>> What happens if you change it to :
>> trans_date between (now() - $2) and now()
>
> That shouldn't change the results...
>
> I think the real issue here is what is the datatype of parameter $2.
> It's apparently intended to be an interval (so that now() - interval
> is a timestamp, and the comparison is meaningful.) But the new system
> is evidently taking $2 as a timestamp: the timestamp minus timestamp
> operator yields an interval (which is unsurprising if you think about
> what it means), and that leads to the reported error when the parser
> tries to figure out what the BETWEEN means.
>
> I would bet that in the old system $2 was explicitly marked as an interval
> value, but in the new system $2 isn't getting marked with any particular
> datatype. Given "timestamp minus unknown", the parser will resolve
> the unknown type as timestamp, the same as is on the other side of the
> operator. (Obviously, that heuristic doesn't work all the time, but
> it's right much of the time.)
>
> So I think this is a client-side behavioral change, and what you need to
> look for is how the type of that parameter is getting set, or not.
Agreed though the detail provided shows:
$2=<0 years 0 mons 0 days 1 hours 0 mins 0.00
which leads me to believe the server is indeed getting an interval from the
client...
The OP should also try explicitly casting the parameter symbol in the
original query:
trans_date between now() - ?::interval and now()
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Upgrade-to-Scientific-Linux-6-5-x86-64-breaks-query-tp5812546p5812574.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.