CXJ CXJ - 1 year ago 46
PHP Question

PHP PDO Postgres versus Sqlite column type for count(*)

Using PHP PDO with this SQL statement

SELECT count(*) FROM my_table;

returns an INTEGER with Postgres and a STRING with Sqlite3. That is, if there is one row in the table, Postgres returns (int)
and Sqlite returns

Is this as intended, or is this a bug?

[edit to add below]

In case you want to follow along at home, here's a demonstration script I threw together. I actually encountered this when my PHPUnit tests passed (using in-memory Sqlite as a test fixture) and but my application failed using the production Postrgres database.

function connect($dsn)
try {
$pdo = new \PDO($dsn);
catch (\PDOException $e) {
echo 'New PDO failed: ' . $e->getMessage() . PHP_EOL;
return $pdo;

function doQuery($pdo, $sql)
if ( ($result = $pdo->query($sql)) === false) {
echo "'$sql' failed: " . print_r($pdo->errorInfo(), true) . PHP_EOL;
return $result;

$pgo = connect('pgsql:host=localhost;dbname=postgres');
$sqo = connect('sqlite::memory:');

doQuery($pgo, 'DROP TABLE IF EXISTS');
doQuery($pgo, 'CREATE TABLE ( ii int )');
doQuery($pgo, 'INSERT INTO VALUES (42)');

doQuery($sqo, "ATTACH DATABASE ':memory:' AS public;") or die();
doQuery($sqo, 'DROP TABLE IF EXISTS');
doQuery($sqo, 'CREATE TABLE ( ii int )');
doQuery($sqo, 'INSERT INTO VALUES (42)');

$pgResult = doQuery($pgo, 'SELECT COUNT(ii) FROM foo');
echo 'Postgres: ';

echo 'Sqlite3: ';
$ltResult = doQuery($sqo, 'SELECT COUNT(ii) FROM foo');

Answer Source

This is a side effect of sqlite not having datatypes. Or rather, having what they call the dynamic type system. But quite interestingly

SELECT TYPEOF(b) FROM ( select count(*) as b from my_table) a;

produces integer as the output! So clearly something is being lost in translation from sqlite to php. However it doesn't really matter because in php '1' + 2 gives 3. Because let's not forget, PHP is also a dynamic typed system.