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?

Answer 1

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