Using named placeholders with interval fails in PHP and PostgreSQL

Question

Okay I've confirmed this works explicitly with PHP.

$ php --version
PHP 5.6.16 (cli) (built: Dec 30 2015 15:09:50) (DEBUG)

<pdo version>
pdo_pgsql
PDO Driver for PostgreSQL   enabled
PostgreSQL(libpq) Version   9.4.0
Module version  1.0.2
Revision    $Id: fe003f8ab9041c47e97784d215c2488c4bda724d $

I would like to recreate the following SQL in PHP using PDO:

UPDATE relationships SET status = 4 WHERE created > NOW() - interval '2 seconds';

This script is working:

<?php

$db = new PDO('pgsql:dbname=db;host=localhost;user=stevetauber');
$stmt = $db->prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - interval '?'");
$stmt->execute(array("2 seconds"));

Here it is with named placeholders:

<?php

$db = new PDO('pgsql:dbname=db;host=localhost;user=stevetauber');
$stmt = $db->prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - interval ':blah'");
$stmt->execute(array(":blah" => "2 seconds"));

Which gives this error:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: :blah in ... line 5

Now according to PHP documentation,

Example #6 Invalid use of placeholder:

 <?php
 $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
 $stmt->execute(array($_GET['name']));

 // placeholder must be used in the place of the whole value
 $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
 $stmt->execute(array("%$_GET[name]%"));
 ?>

Here is the updated code:

<?php

$db = new PDO('pgsql:dbname=db;host=localhost;user=stevetauber');
$stmt = $db->prepare("UPDATE relationships SET status = 4 WHERE created > NOW() - :blah");
$stmt->execute(array(":blah" => "interval '2 seconds'"));

Which yields these DB errors (no script errors):

ERROR:  operator does not exist: timestamp with time zone > interval at character 51
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  UPDATE relationships SET status = 4 WHERE created > NOW() - $1

PDO is doing something weird here though because:

# select NOW() - interval '2 seconds' as a , pg_typeof(NOW() - interval '2 seconds') as b;
               a               |            b             
-------------------------------+--------------------------
 2015-12-30 18:02:20.956453+00 | timestamp with time zone
(1 row)

So how do I use named placeholders with PostgreSQL and interval?

Why do you want to use named placeholders? From PDO wiki Use named placeholders only if you need a complex query or if you already have an associative array which keys are equal to table field names. Otherwise, regular placeholders are simpler to use. — david strachan
– david strachan, Commented Dec 30, 2015 at 20:35
What about UPDATE relationships SET status = 4 WHERE created > NOW() - INTERVAL :bla and binding :bla as 2 seconds? — julp
– julp, Commented Dec 30, 2015 at 21:53
@davidstrachan I've greatly simplified the query to simplify the problem. My real query is extremely long and complicated — Steve Tauber
– Steve Tauber, Commented Dec 31, 2015 at 11:38
@julp Postgres requires the quotations, there is an error with that format. — Steve Tauber
– Steve Tauber, Commented Dec 31, 2015 at 11:39
After test, like Daniel Vérité said, "at least" a cast is needed (UPDATE relationships SET status = 4 WHERE created > NOW() - :bla::interval with :bla binded as 2 seconds) — julp
– julp, Commented Dec 31, 2015 at 15:46

Daniel Vérité · Accepted Answer · 2015-12-30 22:24:50Z

8

Placeholders are for pure values, not for values decorated with units (or with anything else).

To express interval '2 seconds' in a placeholder, there are two options:

in the query, write :secs * interval '1 second' and bind :secs to a number in php
or write: cast(:mystring as interval), and bind :mystring to the string '2 seconds'. It will be interpreted dynamically through the explicit cast.

When experimenting with the psql command line client to compare with the PDO driver, use the PREPARE and EXECUTE SQL statements with postgres native $N placeholders, as opposed to having the parameters values already written literally in the query. This will match what the PHP driver is essentially doing when PDO::ATTR_EMULATE_PREPARES is set to false.

In the last part of you question, when trying this in psql (your query, just simplified to not need a table):

select now() > now() - interval '2 seconds';

it does work and returns 't' (true).

But if you tried that:

prepare p as select now() > now() - $1;

if would fail with

ERROR: operator does not exist: timestamp with time zone > interval

which is the same error as with PDO's prepare/execute.

On the other hand, this does work:

=> prepare p as select now() > now() - interval '1 second'*$1;
PREPARE

=> execute p(2);
?column? 
----------
t

edited Dec 30, 2015 at 22:24

answered Dec 30, 2015 at 22:13

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

4 Comments

Steve Tauber Over a year ago

The multiplication seems hacky to me. Is there anyway to get this to be properly casted: prepare p as select now() > now() - $1;?

Steve Tauber Over a year ago

Okay so this works: prepare p as select now() > now() - interval '$1'; I think the reason is because now() - $1 expects $1 to be a timestamp and timestamp [math] timestamp is always an interval. How can I get PHP to send this prepare statement?

Daniel Vérité Over a year ago

@Steve: I don't think your proposal works. If you execute p('-2 minutes') you get a wrong result. Same thing if you execute p('whatever'). I'm not even sure what it computes.

Daniel Vérité Over a year ago

@Steve: the multiplication is not a kludge, it's the generally recommended solution. See stackoverflow.com/questions/14465727/… as well

Collectives™ on Stack Overflow

Using named placeholders with interval fails in PHP and PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related