Thread: 0/1 vs true/false

0/1 vs true/false

From
Jean-Christian Imbeault
Date:
Just having a small argument with an application developer ...

is using 0/1 for boolean types SQL compliant? I am trying to convince
him that the proper SQL compliant (and postgresql compliant) syntax is
true/false but he won't budge ...

The app as currently written no longer works with postgres because they
code uses 0/1 instead of the now enforced true/false for boolean types.

Can someone point me to an SQL spec and section where this is clearly
stated out?

Thanks,

Jean-Christian Imbeault


Re: 0/1 vs true/false

From
Chris Travers
Date:
I ran into this problem some time ago. I ended up using a query with a
CASE statement in it to caste the boolean as a 1 or 0. I guess one could
do this as a view as well.

Jean-Christian Imbeault wrote:

> Just having a small argument with an application developer ...
>
> is using 0/1 for boolean types SQL compliant? I am trying to convince
> him that the proper SQL compliant (and postgresql compliant) syntax is
> true/false but he won't budge ...
>
> The app as currently written no longer works with postgres because
> they code uses 0/1 instead of the now enforced true/false for boolean
> types.
>
> Can someone point me to an SQL spec and section where this is clearly
> stated out?
>
> Thanks,
>
> Jean-Christian Imbeault
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>



Re: 0/1 vs true/false

From
Peter Eisentraut
Date:
Jean-Christian Imbeault writes:

> is using 0/1 for boolean types SQL compliant?

No.

> Can someone point me to an SQL spec and section where this is clearly
> stated out?

Since it's not compliant, you won't find it anywhere in the standard.

--
Peter Eisentraut   peter_e@gmx.net

Re: 0/1 vs true/false

From
Jean-Christian Imbeault
Date:
Peter Eisentraut wrote:
>
> Since it's not compliant, you won't find it anywhere in the standard.

Oops, I meant to say can someone point me to a ressource (SQL standard
and section?) which states that true/false must be used for booleans :)

Jean-Christian Imbeault


Re: 0/1 vs true/false

From
"Nigel J. Andrews"
Date:

I had do do this sort of thing for a some developers. It was actually 'true'
and 'false' that was wanted not the 0/1. I wrote a little plgsql function and
installed it as a cast to text.

--
Nigel J. Andrews


On Wed, 23 Jul 2003, Chris Travers wrote:

> I ran into this problem some time ago. I ended up using a query with a
> CASE statement in it to caste the boolean as a 1 or 0. I guess one could
> do this as a view as well.
>
> Jean-Christian Imbeault wrote:
>
> > Just having a small argument with an application developer ...
> >
> > is using 0/1 for boolean types SQL compliant? I am trying to convince
> > him that the proper SQL compliant (and postgresql compliant) syntax is
> > true/false but he won't budge ...
> >
> > The app as currently written no longer works with postgres because
> > they code uses 0/1 instead of the now enforced true/false for boolean
> > types.
> >
> > Can someone point me to an SQL spec and section where this is clearly
> > stated out?
> >
> > Thanks,
> >
> > Jean-Christian Imbeault



Re: 0/1 vs true/false

From
Jean-Christian Imbeault
Date:
Nigel J. Andrews wrote:
>
> I had do do this sort of thing for a some developers. It was actually 'true'
> and 'false' that was wanted not the 0/1. I wrote a little plgsql function and
> installed it as a cast to text.

Thanks. I know that there are workarounds, I just want to "prove" that
0/1 is not standards compliant. If I can do that I am pretty sure that
the developer will have nothing against changing to true/false.

Right not he just things postgresql must be broken because 0/1 works in
MySQL ... But he's pretty good when it comes to fixing things because
they don't follow standards. So if I can show him his SQL isn't
standards compliant he'll probably happily fix it.

Jean-Christian Imbeault


Re: 0/1 vs true/false

From
Jochem van Dieten
Date:
Jean-Christian Imbeault wrote:
> Just having a small argument with an application developer ...
>
> is using 0/1 for boolean types SQL compliant? I am trying to convince
> him that the proper SQL compliant (and postgresql compliant) syntax is
> true/false but he won't budge ...
>
> The app as currently written no longer works with postgres because they
> code uses 0/1 instead of the now enforced true/false for boolean types.
>
> Can someone point me to an SQL spec and section where this is clearly
> stated out?

Would this be what you are looking for:

ISO/IEC 9075-2:1999 (E) ©ISO/IEC
5.3 <literal>
(..)
<boolean literal> ::=
     TRUE
     | FALSE
     | UNKNOWN


Additionally about UNKNOWN:

ISO/IEC 9075-2:1999 (E) ©ISO/IEC
4.6 Boolean types
The data type boolean comprises the distinct truth values true
and false . Unless prohibited by a NOT NULL constraint, the
boolean data type also supports the unknown truth value as the
null value. This specification does not make a distinction
between the null value of the boolean data type and the unknown
truth value that is the result of an SQL <predicate>, <search
condition>, or <boolean value expression>; they may be used
interchangeably to mean exactly the same thing.

HTH,
Jochem




Re: 0/1 vs true/false

From
Franco Bruno Borghesi
Date:
This makes me wonder, what about 't' and 'f'?... will they disappear in newer versions of postgreSQL?

On Wed, 2003-07-23 at 09:21, Jochem van Dieten wrote:
Jean-Christian Imbeault wrote:
> Just having a small argument with an application developer ...
> 
> is using 0/1 for boolean types SQL compliant? I am trying to convince 
> him that the proper SQL compliant (and postgresql compliant) syntax is 
> true/false but he won't budge ...
> 
> The app as currently written no longer works with postgres because they 
> code uses 0/1 instead of the now enforced true/false for boolean types.
> 
> Can someone point me to an SQL spec and section where this is clearly 
> stated out?

Would this be what you are looking for:

ISO/IEC 9075-2:1999 (E) ©ISO/IEC
5.3 <literal>
(..)
<boolean literal> ::=    TRUE    | FALSE    | UNKNOWN


Additionally about UNKNOWN:

ISO/IEC 9075-2:1999 (E) ©ISO/IEC
4.6 Boolean types
The data type boolean comprises the distinct truth values true 
and false . Unless prohibited by a NOT NULL constraint, the 
boolean data type also supports the unknown truth value as the 
null value. This specification does not make a distinction 
between the null value of the boolean data type and the unknown 
truth value that is the result of an SQL <predicate>, <search 
condition>, or <boolean value expression>; they may be used 
interchangeably to mean exactly the same thing.

HTH,
Jochem




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html
Attachment

Re: 0/1 vs true/false

From
"Claudio Lapidus"
Date:
> Can someone point me to an SQL spec and section where this is clearly
> stated out?
>

You may want to review the files mentioned at
http://archives.postgresql.org/pgsql-sql/2000-04/msg00118.php

hth
cl.

Re: 0/1 vs true/false

From
Tom Lane
Date:
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> Oops, I meant to say can someone point me to a ressource (SQL standard
> and section?) which states that true/false must be used for booleans :)

Well, there is no boolean type per se in SQL92.  But there is in SQL99.
I think the most relevant part of the spec is the definition of boolean
literals in section 5.3:

         <boolean literal> ::=
                TRUE
              | FALSE
              | UNKNOWN

Note that the spec only really speaks to the question of what booleans
look like when written as constants in SQL statements.  AFAICT it does
not take a position on what representations are to be used when
transferring data into or out of the database.  So using '0' or '1' as
input to a boolean field is not contrary to spec, but merely outside
the spec (and in fact we will accept those strings for boolean...)

            regards, tom lane

Re: 0/1 vs true/false

From
Date:
I doubt it, same way '0' and '1' have not disappeared despite 0 and 1 have.  There is still a function that can convert a string to a boolean.  There is no function to convert an integer to a boolean, which is why 0 and 1 do not work but '0', 'f', '1', 't' all work.
 

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085

 
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Franco Bruno Borghesi
Sent: Wednesday, July 23, 2003 10:21 AM
To: Jochem van Dieten
Cc: Jean-Christian Imbeault; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 0/1 vs true/false

This makes me wonder, what about 't' and 'f'?... will they disappear in newer versions of postgreSQL?

On Wed, 2003-07-23 at 09:21, Jochem van Dieten wrote:
Jean-Christian Imbeault wrote:
> Just having a small argument with an application developer ...
> 
> is using 0/1 for boolean types SQL compliant? I am trying to convince 
> him that the proper SQL compliant (and postgresql compliant) syntax is 
> true/false but he won't budge ...
> 
> The app as currently written no longer works with postgres because they 
> code uses 0/1 instead of the now enforced true/false for boolean types.
> 
> Can someone point me to an SQL spec and section where this is clearly 
> stated out?

Would this be what you are looking for:

ISO/IEC 9075-2:1999 (E) ©ISO/IEC
5.3 <literal>
(..)
<boolean literal> ::=    TRUE    | FALSE    | UNKNOWN


Additionally about UNKNOWN:

ISO/IEC 9075-2:1999 (E) ©ISO/IEC
4.6 Boolean types
The data type boolean comprises the distinct truth values true 
and false . Unless prohibited by a NOT NULL constraint, the 
boolean data type also supports the unknown truth value as the 
null value. This specification does not make a distinction 
between the null value of the boolean data type and the unknown 
truth value that is the result of an SQL <predicate>, <search 
condition>, or <boolean value expression>; they may be used 
interchangeably to mean exactly the same thing.

HTH,
Jochem




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html

Re: 0/1 vs true/false

From
Tom Lane
Date:
Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:
> This makes me wonder, what about 't' and 'f'?... will they disappear in
> newer versions of postgreSQL?

No.  See my comment about I/O representations being outside the spec.
There is no inconsistency in the following examples:

regression=# select true;
 bool
------
 t
(1 row)

regression=# select t;
ERROR:  attribute "t" not found
regression=# select 't'::boolean;
 bool
------
 t
(1 row)

regression=# select '1'::boolean;
 bool
------
 t
(1 row)


            regards, tom lane

Re: 0/1 vs true/false

From
Peter Eisentraut
Date:
Tom Lane writes:

> Note that the spec only really speaks to the question of what booleans
> look like when written as constants in SQL statements.  AFAICT it does
> not take a position on what representations are to be used when
> transferring data into or out of the database.  So using '0' or '1' as
> input to a boolean field is not contrary to spec, but merely outside
> the spec (and in fact we will accept those strings for boolean...)

The transfer into the database is regulated when you write the data into
the SQL statement (as you normally would).  Input through prepare/bind and
input is governed by the respective client interface (e.g., embedded SQL
language bindings, JDBC spec, ODBC spec).  If you use libpq or psql then
you're beyond all standards anyway.

--
Peter Eisentraut   peter_e@gmx.net

Re: 0/1 vs true/false

From
"scott.marlowe"
Date:
On Wed, 23 Jul 2003, Tom Lane wrote:

> Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> > Oops, I meant to say can someone point me to a ressource (SQL standard
> > and section?) which states that true/false must be used for booleans :)
>
> Well, there is no boolean type per se in SQL92.  But there is in SQL99.
> I think the most relevant part of the spec is the definition of boolean
> literals in section 5.3:
>
>          <boolean literal> ::=
>                 TRUE
>               | FALSE
>               | UNKNOWN

Was it pulled from SQL92 before it went standard?  My copy of the
pre-release lists a boolean type, just like the 99 standard does.

further, intermediate SQL compliance has this in it:

24)Subclause 8.12, "<search condition>":

            a) A <boolean test> shall not specify a <truth value>.

Does that mean you should only use the "is true" suntax, not the =true?




Re: 0/1 vs true/false

From
Tom Lane
Date:
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Wed, 23 Jul 2003, Tom Lane wrote:
>> Well, there is no boolean type per se in SQL92.  But there is in SQL99.

> Was it pulled from SQL92 before it went standard?  My copy of the
> pre-release lists a boolean type, just like the 99 standard does.

Where?  SQL92 says nothing about a declarable boolean datatype that
I can see.  They're a bit schizophrenic in that they do define a lot
of operators that are described as returning boolean ... but you cannot
create a column of type boolean, nor is there a boolean-literal construct.
AFAICS, boolean values can only exist "in flight" between operators and
a WHERE or HAVING clause in SQL92.

            regards, tom lane

Re: 0/1 vs true/false

From
"scott.marlowe"
Date:
On Wed, 23 Jul 2003, Tom Lane wrote:

> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > On Wed, 23 Jul 2003, Tom Lane wrote:
> >> Well, there is no boolean type per se in SQL92.  But there is in SQL99.
>
> > Was it pulled from SQL92 before it went standard?  My copy of the
> > pre-release lists a boolean type, just like the 99 standard does.
>
> Where?  SQL92 says nothing about a declarable boolean datatype that
> I can see.  They're a bit schizophrenic in that they do define a lot
> of operators that are described as returning boolean ... but you cannot
> create a column of type boolean, nor is there a boolean-literal construct.
> AFAICS, boolean values can only exist "in flight" between operators and
> a WHERE or HAVING clause in SQL92.

Yep, you're right.  I was looking at how they treated booles in search
conditions, and figured they had a type to match.


Re: 0/1 vs true/false

From
Dennis Gearon
Date:
<meta http-equiv="Content-Type"
 content="text/html;charset=ISO-2022-JP">



I have looked around, but not found the standard. Where I have looked,
it seems to be:

    t/f,
     ..... not........
    true/flase
    0/1
    yes/no

Jean-Christian Imbeault wrote:

  Nigel J. Andrews wrote:


    I had do do this sort of thing for a some developers. It was actually 'true'
and 'false' that was wanted not the 0/1. I wrote a little plgsql function and
installed it as a cast to text.



Thanks. I know that there are workarounds, I just want to "prove" that
0/1 is not standards compliant. If I can do that I am pretty sure that
the developer will have nothing against changing to true/false.

Right not he just things postgresql must be broken because 0/1 works in
MySQL ... But he's pretty good when it comes to fixing things because
they don't follow standards. So if I can show him his SQL isn't
standards compliant he'll probably happily fix it.

Jean-Christian Imbeault


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

Re: 0/1 vs true/false

From
Franco Bruno Borghesi
Date:
and what about boolean attirbutes in where clauses? Is any difference between

-SELECT ... WHERE boolean_field
and
-SELECT ... WHERE boolean_field=true
?

On Wed, 2003-07-23 at 11:48, Tom Lane wrote:
Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:
> This makes me wonder, what about 't' and 'f'?... will they disappear in
> newer versions of postgreSQL?

No.  See my comment about I/O representations being outside the spec.
There is no inconsistency in the following examples:

regression=# select true;bool
------t
(1 row)

regression=# select t;
ERROR:  attribute "t" not found
regression=# select 't'::boolean;bool
------t
(1 row)

regression=# select '1'::boolean;bool
------t
(1 row)

		regards, tom lane
Attachment

Re: 0/1 vs true/false

From
Greg Stark
Date:
Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:

> This makes me wonder, what about 't' and 'f'?... will they disappear in
> newer versions of postgreSQL?

Nothing to do with the spec, but can I toss one comment in here:

 Having booleans printed as 't' and 'f' is the single most annoying thing in
 all of postgres.

I swear it has caused more bugs in my code than any single factor. The problem
is that any language driver that just treats all columns as text -- which is
presumably most if not all -- will receive the column as 't' and 'f'. Both of
which evaluate to true in virtually every language.

If it printed booleans as 0 and 1 then pretty much every language would be
able to interpret them properly.

Instead I have to have =='f' and =='t' strewn throughout my code everywhere
making it harder to read and extremely fragile. If I forget one anywhere I
silently get subtly broken semantics.

This problem might be reduced with the binary protocol since the language
drivers will be able to ship boolean values in binary format and allocate
variables of the appropriate type in the language. But still a lot of drivers
will just bind everything as text anyways, and the goal when casting values to
text should be to pick values that get interpreted appropriately.

--
greg

Re: 0/1 vs true/false

From
Andrew Ayers
Date:
Greg Stark wrote:

> Instead I have to have =='f' and =='t' strewn throughout my code everywhere
> making it harder to read and extremely fragile. If I forget one anywhere I
> silently get subtly broken semantics.

Why did you do that? Why not create a single function (isTrue()?) that
you pass the field through and let the function evaluate it. It doesn't
solve the issue, but it would make your code more stable, and if things
ever changed (ie, from a "t" to a "1" or something) - you just update
that one function.

In fact (and my C/C++ memory is hazy) - you should be able to create a
set of functions or methods in you code/classes that could take a
variety of data types as the passed type, and have each of those funnel
down to one type to evaluate and pass up the chain - so you could call
the function isTrue(int) or isTrue(char *) or something, and it would
always work OK (ie, a polymorphic function/method).

That way, your code could always call the *same* function/method, and
always know it was going to return the same value - but behind the
scenes, it would be doing all the "funky exercises" needed to return the
proper value. If it ever radically changes (or you change the DB), you
change one function, and the system keeps working (no having to hunt and
change 'n' lines of code).

Yes, there would be a little more overhead - but future maintenance
coders (or yourself, down the line) will thank you for your "foresight".

Andrew L. Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.