PHP PDO PostgreSQL Could Not Determine Data Type

Question

I've been getting a strange error while using the PHP PDO with PostgreSQL for a specific query and I don't understand why.

I have the following code

$conn = new PDO($db, $us, $pw);

try {
   $count = $conn->prepare("select count(idno) as count from t_dummy where ( ? is null or idno = ? )");
   $count->execute([null, null]);
   $count_fetch = $count->fetch();
} catch(Exception $error) {
   echo $error;
}

var_dump($count_fetch[0]);

If the PDO is connected to a MySQL Database then it works fine but when connected to a PostgreSQL Database it fails with the following error

could not determine data type of parameter $1

It looks like it doesn't like taking a parameter for ? is null but I don't know why as this is valid PostgreSQL syntax that works fine in PgAdmin

Answer 1

Use a CAST():

$conn = new PDO($db, $us, $pw);

try {
   $count = $conn->prepare("SELECT COUNT(idno) AS count FROM t_dummy WHERE ( CAST(? AS integer) IS NULL OR idno = ? );");
   $count->execute([null, null]);
   $count_fetch = $count->fetch();
} catch(Exception $error) {
   echo $error;
}

var_dump($count_fetch[0]);