PostgreSQL - variable binding and date adding

Question

I need to update some timestamp columns in a table in a Postgres database (8.3).

My query (simplified) is as follows:

update table1 set dateA = dateA + interval '10 hours' where id = 1234; 

This is part of the script, and much needs to be updated there, so I prefer using bind variables rather than creating a query string every time. This means that my request will look like this:

 update table1 set dateA = dateA + interval '? hours' where id = ?; 

When I do this, the complaint is that I put 2 variable bindings when only one is required.

Should I try to fit ? out of quotes:

 update table1 set dateA = dateA + interval ? ' hours' where id = ?; 

I get:

 ... syntax error at or near "' hours'" 

It looks like the request has been interpreted as

 ... dateA = dateA + interval '10' ' hours' ... 

I cannot find anything in the documentation to help ... any suggestions?

thanks

Answer 1

Try the following:

 update table1 set dateA = dateA + ( (interval '1 hours') * ? ) where id = ?; 

Or that:

 update table1 set dateA = dateA + cast(? || ' hours' as interval) where id = ?;