2

I have a boolean column in a PostgreSQL table. In pgAdmin III, the table view shows 'TRUE' and 'FALSE' for the column. When I do a SQL 'select *', that other window shows 't' and 'f'. Is this some configuration problem? Shouldn't it also show 'TRUE' and 'FALSE'?

Then we come to PHP. I have the following code to deal with this boolean (actually I'm doing something different, but simplified it here):

$q = "select * from tax where id = $1";
$res = pg_query_params($conn,$q,[$id]);
if ($res) {
    $row = pg_fetch_array($res,NULL,PGSQL_ASSOC);
    $valido = $row['valido'];
    echo $valido; // prints 'f' (== 'FALSE' on database)
    if ($valido) {
        echo 'valid'; // gets here
    } else {
        echo 'invalid';
    }
}

The database says 'FALSE', php reads 'f' and treat it as 'TRUE'. Is this some faulty configuration, or php always reads a boolean value as a text interpreted as the opposite of the original value?

Rodrigo
  • 4,706
  • 6
  • 51
  • 94
  • `f` is a string, not a Boolean: Cast it to a Boolean if you want to use it as a Boolean - `$valido = ($row['valido'] == 'f') ? false : true;` – Mark Baker Oct 26 '15 at 14:27
  • So I need to configure PostgreSQL to return the boolean as a boolean? Or the configuration may be done on PHP itself? Or it will always return the boolean as a string and I need to always have the additional job of return it to boolean again? Wouldn't it be wiser to simply keep it boolean? – Rodrigo Oct 26 '15 at 14:28
  • @MarcB, it would not be a duplicate if (who? PHP or PostgreSQL) didn't return a string where the original value is a boolean. Is this behavior unalterable? It's the opposite of a good, neat, economic programming logic. – Rodrigo Oct 26 '15 at 14:33
  • no idea where a pg boolean would become a PHP f, but regardless, your `f` is a string, it's not empty, therefore it's a "true" value. – Marc B Oct 26 '15 at 14:35
  • Where the boolean becomes a string? In the code above. Are you telling me isn't it normal behavior? Then, where can I configure it to do the right way? (I know how php evaluates string to boolean, I'm asking why did it became a string in first place, and if this behavior can be changed. So it's a rather different question, don't you think? I changed the title, please take a look.) – Rodrigo Oct 26 '15 at 14:56
  • Don't you agree that's a different question? Or, since you have 200k+ reputation points you don't have time to read things WITH ENOUGH ATTENTION, @MarcB? – Rodrigo Oct 26 '15 at 15:32
  • @MarcB It is a different question, I nominated it for reopening. – Marek Oct 26 '15 at 17:20
  • @Rodrigo It has been like this since forever, so for backward compatibility it stays this way. PDO returns 0/1 for false/true. – Marek Oct 26 '15 at 17:21
  • Yeah, now that it's "at which point does boolean become string" is definitely not dupe anymore. – Marc B Oct 26 '15 at 17:39
  • For MySQL, there exists a native driver which is able to represent MySQL's types (such as numbers) correctly. I *think* that the same doesn't exist for Postgres, so everything that Postgres returns is treated as a string (since there is no information how to represent it in PHP). Again, I *think* that you cannot configure anything so that Postgres types are correctly represented in PHP out of the box. You'll have to perform comparisons after retrieving the information and coercing stuff manually into correct types. – N.B. Oct 26 '15 at 17:47
  • @Marek, if it returned 0/1 it would be less bad, since PHP already interpret this as boolean in the right way. The problem is that PostgreSQL seems to be returning a string 'f' or 't' (at least inside pgAdmin SQL builder), which is incompatible with php string-boolean logic. – Rodrigo Oct 26 '15 at 18:53

1 Answers1

1

Let's face it:

  • Data returned by either PDO or the pgsql libraries are always strings.
  • Postgresql representation for booleans are strings 't' and 'f'.

This is specified by the PHP documentation in the "returned values" of the different fetch functions. here and here.

If you want these results to be cast to PHP equivalent types, you must set up a converter system.

Community
  • 1
  • 1
greg
  • 3,354
  • 1
  • 24
  • 35