PHP and PostgreSQL standard types in the query result. Why is it always strings?

Question

Using standard php5-pgsql driver it results to me that, at least with the default php settings on ubuntu 10.04, the result values are not mapped to the standard php types. That is

postresql::integer => php::int
postresql::real => php::float
etc

And return the result values being cast to strings. It creates sometimes quite a lot of headache when the locale of system does not coincide with the locale of database etc.

Is it possible somehow to get the appropriate casting in php? If not, any insight on why would very welcome!

EDIT:

The PDO solved the "postresql::integer=>php::int", but not for ex. "postresql::real=>php::float" it seems because of that PDO uses the native drivers that is pgsql as I understand. The issue with encoding is not a problem in my case, the only need is to map types exactly or at least as close as possible!

$sql = 'SELECT integer_field, numeric_field_10_2 FROM table';
foreach ($dbh->query($sql) as $row) {
    var_dump($row);
}
----------------------------------------
array(4) {
  ["integer_field"]=>int(79)
  ["numeric_field_10_2"]=>string(6) "100.12"
}

Thanks, I.

The LDAP extension also has this irritating "feature". Maybe the PDO driver will fix it? I don't know either way, but worth a try... — DaveRandom
– DaveRandom, Commented Jan 19, 2012 at 17:47

Mike Buland · Accepted Answer · 2012-01-19 17:51:06Z

3

This is an issue inherited from the C API, and that from the postgres client protocol. The results are provided as strings in the backend library. It shouldn't be a problem with compatible encodings, but you're right, it can be a problem.

You can use manual casting, or trust the automatics in php, but the real issue is just getting the encoding setup correctly. You can set postgres' encoding on a per-database or per-cluster basis, I recommend using UTF-8, personally.

Converting an existing database is quite a hassle, it usually involves a full dump and full import into a new database.

pg_client_encoding will tell you the current postgres encoding, but won't help with the php encoding. If you can't use compatible encoding, maybe it would be worth creating a wrapper that uses iconv to convert the string data.

Finally, you could use an alternative client library. I believe the PDO postgres library for php handles this better.

http://us.php.net/manual/en/book.pdo.php

answered Jan 19, 2012 at 17:51

Mike Buland

7314 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Buland Over a year ago

I just did a conclusive test, PDO does provide better, easier to use results, and is more general. I recommend using the PDO drivers.

Igor Over a year ago

Question: "issue inherited from the C API" you meant the php5-pgsql driver or some other? Do you have any refs?

Collectives™ on Stack Overflow

PHP and PostgreSQL standard types in the query result. Why is it always strings?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related