The scenario
Two systems(not server) running PHP and PostgreSQL with the following versions
-
Fedora 15:
PHP
PHP 5.3.13 (cli) (built: May 9 2012 14:38:35)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
pdo_pgsql
PostgreSQL(libpq) Version 9.0.7
Module version 1.0.2
PostgreSQL
PostgreSQL 9.1.4
With CITEXT extension enabled. -
ArchLinux:
PHP
PHP 5.4.6 (cli) (built: Aug 16 2012 12:50:09)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
pdo_pgsql
PostgreSQL(libpq) Version 9.1.4
Module version 1.0.2
PostgreSQL
PostgreSQL 9.1.4
With CITEXT extension enabled.
when a simple query such as
select column1 from schema1.table1 where column1= ?
Where column1 is of the type CITEXT, is executed through PHP PDO
- On Fedora with PHP 5.3.13,libpq 9.0.7, the query performs as expected with CITEXT(a case insensitive search occurs).
- On ArchLinux with PHP 5.4.6,libpq 9.1.4, the query does not perform as expected with CITEXT(a case sensitive search occurs).
I am guessing the newer version of PHP PDO libs are doing something similar to this:
select column1 from schema1.table1 where column1= 'value'::text;
during binding.
- Am I right?
- Is there a workaround? Otherwise, the use of CITEXT as a column data type to gain the advantage of case insensitive search is useless when using newer versions of PDO.
Update
After turning statement level logging on, on ArchLinux with PHP 5.4.6,libpq 9.1.4 :
LOG: execute pdo_stmt_00000001: select column1 from schema1.table1 where column1 = $1
DETAIL: parameters: $1 = 'value'
LOG: statement: DEALLOCATE pdo_stmt_00000001
where the actual value of the column column1
is VALUE
.
Still comes back with 0 elements.
When the statement
select column1 from schema1.table1 where column1 = 'value';
is executed directly on the PSQL
prompt comes back with a single row.
column1
---------
VALUE
(1 row)
So, the type casting does not take place! I still am not able to understand the behaviour of PDO
/postgresql
.
Update 2012-08-27 16:15:43.669142+00 (UMT + 0)
After trying to directly execute a query without preparing a statement.
Here is the code that was used to test :
try {
$db = new PDO('pgsql:dbname=database1;user=user;password=pass;host=localhost');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::citext ";
$retval=$db->query($sql);
foreach ($retval as $row) {
print $row['uname'] . '<br>';
}
}catch (PDOException $PDOerr) {
echo 'An error occured : <br>';
var_dump($PDOerr);
exit;
//some thing went wrong while performing the action on db.
}
I get the error :
object(PDOException)#10 (8) { ["message":protected]=> string(211) "SQLSTATE[42704]: \
Undefined object: 7 ERROR: type "citext" does not exist LINE 1: ...
I do not understand why citext
is not getting detected!
When the statement is executed directly on the PSQL
prompt, everything works fine as mentioned above.
Best Answer
Locate the namespace inside which the
citext
type resides:Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to
citext
:If that solves the problem but in a way you find inelegant, you might reconsider how you're using schemas and
search_path
: make sure that all your custom types are accessible no matter what.