Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL - bind variables and date addition

Tags:

postgresql

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

like image 548
azp74 Avatar asked May 11 '10 06:05

azp74


People also ask

How do I bind variables in PostgreSQL?

In PostgreSQL, bind variables are numbers preceeded by a $ sign. When using SQL Relay bind functions, to refer to an Oracle, Sybase or MS SQL Server bind variable, you should use its name without the preceeding colon.

What is bind in PostgreSQL?

BIND is the second phase of the PARSE / BIND / EXECUTE extended query protocol in PostgreSQL; see message flow in the docs. To work out what it's doing in the bind phase and why it's taking so long, you'll need to use a tool like gdb to attach to a process and get a backtrace.

What is interval in PostgreSQL?

In PostgreSQL the interval data type is used to store and manipulate a time period. It holds 16 bytes of space and ranging from -178, 000, 000 years to 178, 000, 000 years. It also has additional attribute called “precision (denoted by p)” that can be used to set the level of precision in the query results.

How do I declare an integer in PostgreSQL?

user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTYPE; myfield tablename. columnname%TYPE; arow RECORD; The general syntax of a variable declaration is: name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];


1 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 = ?;
like image 181
Michael Buen Avatar answered Sep 20 '22 15:09

Michael Buen