Bug #70789 [Opn->Nab]: Datatype mismatch for postgres expression

Edit report at https://bugs.php.net/bug.php?id=70789&edit=1 ID: 70789 Updated by: mbeccati@php.net Reported by: stefan at php-engineer dot de Summary: Datatype mismatch for postgres expression -Status: Open +Status: Not a bug Type: Bug Package: PDO PgSQL Operating System: Ubuntu 14.04.1 LTS PHP Version: 5.6.14 -Assigned To: +Assigned To: mbeccati Block user comment: N Private report: N New Comment: Thanks for your bug submission. I'm happy to report it's not a PDO_pgsql bug. Nor a PostgreSQL bug, to be fair. In fact if you try to prepare such a query from psql you will see the very same error. postgres=# PREPARE foo AS UPDATE categories SET priority = (CASE WHEN id = $1 THEN $2 END) WHERE id in ($3); ERROR: column "priority " is of type integer but expression is of type text You just need to cast the CASE expression, e.g. (CASE WHEN id = $1 THEN $2 END)::int, or (CASE WHEN id = $1 THEN $2::int END), or (CASE WHEN id = $1 THEN $2 ELSE 0 END) for Postgres to know it's an integer and accept it. Previous Comments: ------------------------------------------------------------------------ [2015-10-26 10:40:27] stefan at php-engineer dot de Description: ------------ More information about this issue: https://github.com/cakephp/cakephp/issues/7534 Test script: --------------- $db = new PDO('pgsql:host=localhost;dbname=cake_test_db;user=markstory'); $stmt = $db->prepare('UPDATE categories SET priority = (CASE WHEN id = :c0 THEN :c1 END) WHERE id in (:c2)'); $stmt->bindValue('c0', '52852e5c-5761-4b87-91d3-456f1dff1469', PDO::PARAM_STR); $stmt->bindValue('c1', 2, PDO::PARAM_INT); $stmt->bindValue('c2', '52852e5c-5761-4b87-91d3-456f1dff1469', PDO::PARAM_STR); $out = $stmt->execute(); var_dump($out); var_dump('Error code', $stmt->errorCode()); var_dump('Error info', $stmt->errorInfo()); Actual result: -------------- SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "priority" is of type integer but expression is of type text LINE 1: UPDATE categories SET priority = (CASE WHEN id = $1 THEN $2 ... ^ HINT: You will need to rewrite or cast the expression. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=70789&edit=1