# PostgreSQL driver handles boolean values incorrectly...

- Thread: https://externals.io/message/13759
- Original archive: https://news-web.php.net/php.internals/13759
- Started by Sean Chittenden on 2004-11-07T01:20:20+00:00
- Messages: 5
- Reply by email to internals@lists.php.net
- More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

---

## #13759 — Sean Chittenden — 2004-11-07T01:20:20+00:00

URL: https://externals.io/message/13759#13759

Howdy.  Was doing some development with PHP and PostgreSQL and came 
across a rather nasty surprise.

A boolean value is returned as the strings 't' and 'f', not the 
constants true and false.  This presents all kinds of interesting 
oddities for code that does something like:

$r = pg_query("SELECT FALSE");
list($b) = pg_fetch_row($r);
if ($b)
   echo "This gets displayed 100% of the time because %b is the string 
'f'\n";
else
   echo "This never gets displayed: $b is always true\n";

The same problem lies with the `NULL` value, which IMHO, should be mapped 
to the constant NULL, not the string 'NULL'.  I took a first stab at 
handling this, but have been by and large unsuccessful, so I'm posting 
here instead of submitting a complete solution.  My patches can be 
found at the URL below:

http://people.FreeBSD.org/~seanc/patches/php/

Any help/assistance is appreciated.  I thought I was doing the right 
thing, but am apparently not.  Please advise.  Thanks in advance.  -sc

-- 
Sean Chittenden

---

## #13767 — Michael Sims — 2004-11-07T18:38:06+00:00

URL: https://externals.io/message/13759#13767 · In reply to: #13759

Sean Chittenden wrote:
> A boolean value is returned as the strings 't' and 'f', not the
> constants true and false.  This presents all kinds of interesting
> oddities for code that does something like:
[...]

You're probably already aware of this, but you can use a bit(1) field as a
boolean and this will map to PHP values that will allow you to check for
truth in conditionals.  Personally I do that instead of using Postgres's
boolean since other database systems I've worked with don't have a boolean
type, but they all have a bit type.  Although I agree that it would be nice
for PHP to map pg's boolean to PHP's boolean...

> The same problem lies with the `NULL` value, which IMHO, should be
> mapped to the constant NULL, not the string 'NULL'.

I was unable to reproduce this.  The following code:

$r = pg_query($dbh, "create table test (field1 varchar(10))");
$r = pg_query($dbh, "insert into test values (NULL)");
$r = pg_query($dbh, "select field1 from test");

$row = pg_fetch_row($r);
var_dump($row);

$r = pg_query($dbh, "drop table test");

outputs:

array(1) {
  [0]=>
  `NULL` 
}

On PHP 4.3.9, Postgresql 7.4.5, running on Linux (Debian Sarge).  If you get
different results perhaps you've uncovered a bug that should be reported...

---

## #13773 — Daniel Convissor — 2004-11-07T22:00:11+00:00

URL: https://externals.io/message/13759#13773 · In reply to: #13767

> Sean Chittenden wrote:
> > […]

I suspect that behavior has nothing to do with PHP and everything to 
do with PostgreSQL.  Each DBMS outputs their information in their own 
way.

Now, should PHP implement a new standard to convert that into a 
standard format, well that's another question.  The answer is "no."  
That would cause compatibility problems.

Stuff like this will likely be handled by the experimental PDO 
extension.  When it becomes stable and fully develloped, things will 
be very nice.


> You're probably already aware of this, but you can use a bit(1) 
> field as a boolean

Most DBMS's don't support BIT column types.  Second, some of those 
that do don't allow `NULL` in them.  `NULL` is a legit value for a BOOLEAN 
column.

I'll be talking about compatibility issues like this at the 
International PHP Conference this coming Wednesday at 13:30 during my 
talk entitled "Building Truly Portable Database Applications in PHP."
I'll have the slides up later this week.

--Dan

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409

---

## #13794 — Sean Chittenden — 2004-11-08T18:32:57+00:00

URL: https://externals.io/message/13759#13794 · In reply to: #13773

> > […]
>
> I suspect that behavior has nothing to do with PHP and everything to
> do with PostgreSQL.  Each DBMS outputs their information in their own
> way.
>
> Now, should PHP implement a new standard to convert that into a
> standard format, well that's another question.  The answer is "no."
> That would cause compatibility problems.

Bah, provide a backwards compatibility config option.  As the following 
poster suggests, PHP needs some way of advancing itself out of the 
stone age as a programming language.  The use of php.ini for register 
globals sets a precedent to allow this to happen, IMHO.  Something 
like: pgsql_bool_return_string = true for the near future, then change 
over to pgsql_bool_return_string = false at some later date.  My other 
cohorts on this contract had the exact same idea and to me it seems 
more than rather plausible to me.

> > […]
>
> Most DBMS's don't support BIT column types.  Second, some of those
> that do don't allow `NULL` in them.  `NULL` is a legit value for a BOOLEAN
> column.

 `NULL` can be a legit value for any column depending on the DDL.  `NULL` 
can also be an invalid value for any particular column.  I don't see 
how this makes any difference.

-sc

-- 
Sean Chittenden

---

## #13795 — Daniel Convissor — 2004-11-09T01:19:42+00:00

URL: https://externals.io/message/13759#13795 · In reply to: #13794

Hi Sean:


> 
> Bah, provide a backwards compatibility config option.

The PHP extension isn't a place for that.  PDO or some database 
abstraction layer written in PHP is.

Anyway, as mentioned, none of the DBMS's output a boolean data type in 
PHP.  So, why even bother?  Or, why stop at PostgreSQL?


> `NULL` can be a legit value for any column depending on the DDL.  `NULL` 
> can also be an invalid value for any particular column.  I don't see 
> how this makes any difference.

My point was BIT isn't a good mapping for proper boolean 
functionality.

--Dan

Hi Steph :)

-- 
 T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
            data intensive web and database programming
                http://www.AnalysisAndSolutions.com/
 4015 7th Ave #4, Brooklyn NY 11232  v: 718-854-0335 f: 718-854-0409