Postgresql SQLSTATE[42P18]: Indeterminate datatype with PDO and CONCAT

Question

I'm having issues with CONCAT() when used on a WHERE, in PDO.

The code:

<?php
require_once('config.php');

$fdate = '01/01/2010';
$tdate = '31/12/2030';
$identification = '';

$count = "SELECT count(*) as total FROM ( select time_id from doc_sent WHERE date >= :fdate AND date <= :tdate AND identification LIKE concat('%',:identification,'%') ) x;";

//$count = "SELECT count(*) as total FROM ( select time_id from doc_sent WHERE date >= :fdate AND date <= :tdate ) x;";


$stmt_count_row_main_table = $pdo->prepare($count);
$stmt_count_row_main_table->execute(['fdate' => $fdate, 'tdate' => $tdate, 'identification' => $identification]);
//$stmt_count_row_main_table->execute(['fdate' => $fdate, 'tdate' => $tdate]);
$count_row_main_table = $stmt_count_row_main_table->fetch();

print_r( $count_row_main_table);

?>

The code works when the 'identification' part is commented. When I'm trying to use CONCAT(), it doesn't.

I tried many "version" of CONCAT() (and read many other questions, like this one: How do I create a PDO parameterized query with a LIKE statement? ) but I am always referring to the main documentation: https://www.postgresql.org/docs/9.1/static/functions-string.html

Which say:

concat('abcde', 2, NULL, 22) --> abcde222

The FULL error when I use CONCAT() is:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42P18]: Indeterminate datatype: 7 ERROR:  could not determine data type of parameter $3 in /var/www/pdo-reporter/show.php:17\nStack trace:\n#0 /var/www/pdo-reporter/show.php(17): PDOStatement->execute(Array)\n#1 {main}\n  thrown in /var/www/pdo-reporter/show.php on line 17

What's wrong with my code?

Answer 1

CONCAT is a function that takes a VARIADIC argument list, which means that internally postgres will convert them into an array of the same type.

postgres=# \df concat
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type 
------------+--------+------------------+---------------------+------
 pg_catalog | concat | text             | VARIADIC "any"      | func

When trying to resolve the input type to a single type, the SQL parser fails. It can be reproduced in this simpler form:

postgres=# PREPARE p AS select concat('A', $1);
ERROR:  could not determine data type of parameter $1

The parser can't figure out the datatype of $1 so it errs on the side of caution.

One easy solution is to cast the parameter as text:

postgres=# PREPARE p AS select concat($1::text);
PREPARE

or with the CAST operator:

postgres=# PREPARE p AS select concat(cast($1 as text));
PREPARE

I haven't tested with PDO but presumably it would work (given how it deals with parameters to produce prepared statements) to change the query to:

"...identification LIKE '%' || :identification || '::text%'..."

or use the '||' operator instead of concat in the query:

identification LIKE '%' || :identification || '%'

EDIT: BTW if you want to find that a parameter :X is a substring of identification, this clause is more secure: strpos(identification, :X) > 0, because :X may contain '%' or '_' without causing any side-effect in the match, contrary to what happens with LIKE.