On Sat, Mar 3, 2012 at 7:47 PM, <stefano.bacciane...@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 6512 > Logged by: Stefano Baccianella > Email address: stefano.bacciane...@gmail.com > PostgreSQL version: 9.1.1 > Operating system: Windows 7 64bit > Description: > > When trying to execute a query like: > > SELECT * FROM table WHERE > timestamp_column > $1 > AND timestamp_column < $1 + interval '1 hour' > > There is no problems, but when executing > > SELECT * FROM table WHERE > timestamp_column > $1 - interval '1 hour' > AND timestamp_column < $1 + interval '1 hour' > > The database return a error saying the the operator timestamp > interval > does not exist.
This appears to be a type resolution problem. I find that it doesn't matter whether I compare timestamp_column to $1 using > or <, nor does it matter whether I use + to add an interval or - to subtract one. However, if the first reference to $1 in the query is a direct comparison against timestamp_column, then everything is fine; if the first reference involves additional or subtraction of an interval, then it breaks. Here's what I think is happening: when PostgreSQL sees $1 + interval '1 hour' first, it concludes that $1 must be intended to be an interval, so that $1 + interval '1 hour' is also an interval, and that can't be compared to the timestamp column, so it errors out. But when it sees timestamp_column > $1 first, it concludes that $1 must be intended to be a timestamp. After that, when it subsequently sees $1 + interval '1 hour', it's already decided that $1 is a timestamp, so it uses the timestamp + interval operator here rather than interval + interval; that yields a timestamp, so everything's fine. The right fix here is probably to explicitly specify the types you want for the parameters, rather than making PostgreSQL guess. That is, instead of saying: PREPARE x AS SELECT * FROM foo WHERE timestamp_column > $1 - interval '1 hour' AND timestamp_column < $1 + interval '1 hour' Instead do: PREPARE x (timestamp) AS SELECT * FROM foo WHERE timestamp_column > $1 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
