PostgreSQL - bind variables and date addition

Question

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

Michael Buen · Accepted Answer · 2010-05-11 06:27:14Z

10

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 = ?;

answered May 11, 2010 at 6:27

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - bind variables and date addition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related