PostgreSQL - bind variables and date addition

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

My query (simplified) look like this:

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

This is part of a script and there's a lot to update so my preference is to use bind variables, rather than to have to build the query string each time. This means my query becomes:

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

When I do this, the complaint is that I've supplied 2 bind variables when only one is required.

If I try to put the ? outside the quote marks:

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

I get:

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

It looks as though the query has been interpreted as

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

I can't find anything in the documentation to help ... any suggestions?

Thanks

Answers


Try this:

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

Or this:

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

Need Your Help

Stop image to move too far in a navigation

javascript jquery html tweenlite

I create this codepen to make a city navigation with dragabble and arrow navigation, it's work in both directions (right and left) but I need to find a way to stop when it reaches the end of the im...

AS3:How to change a colored Bitmap's BitmapData to black and white?

flash actionscript-3 bitmapdata

How can I change the bitmapdata of a coloured Bitmap to Black and White in AS3 ?