Re: Boolean output format - Mailing list pgsql-general

I certainly see the dillemma with php interpreting 'f' as true.

I think that your current solution of using a smallint type might be the best
way to go. After all, you want the output in int form, right? Add a
constraint like:
CREATE TABLE b(a smallint check(a in (0,1)));

That means that if you try to enter something other than 0 or 1, it throws an
error. You can insert how you like, it only wastes 1 byte (smallint is only 2
bytes), and you can retrieve the result as you like.

And here's the long explanation about why (feel free not to read it :):
    All relational databases, including MySQL, return types as text. Php is
dynamically typed, so when it recieves the text character "0", and you ask it
to interpret the result as a boolean value, it chooses false. So, your
application is already doing some translating.
    Now, imagine from the database perspective: should you set up several
conversion functions for every data type? I haven't even counted the
datatypes in postgres, but there are a lot. You could make a case that most
of those datatypes might be more desirable in a different form in many
situations. I would say that it's impossible to cover all of the possible
situations unless you allowed the user to make an arbitrary function that
converted data on the fly. Well, postgres has that! You can make a
user-defined data type, and specify the exact formats that you'd like to
input the data, store the data, and output the data.

So, you could make a datatype called "zero_or_one" that did exactly as you
say. Easy? Not really. But it's there if you need it (that one byte matters
if you have enough records).

The developers tend to like general solutions, like the user-defined data
types and the constraints. It's generally pretty difficult to get a new SET
variable added, so it's unlikely they'd go for that for just a boolean
conversion.

So, I suggest continuing with smallint (but do add that constraint, you don't
want an inconsistant database). Are there any other reasons you don't want to
use it?

Of course you're right about MySQL: people aren't gonna like porting. I don't
really see a solution, but if you have one I'd be interested to know. The SET
variable makes sense, but it's just that I get the impression the developers
don't like too many of those.

Disclaimer: the developers might have very different views from what I've
implied. Those are just my impressions of their standpoint.

Regards,
    Jeff Davis



On Friday 04 October 2002 06:58 pm, Garo Hussenjian wrote:
> Thanks, Jeff.
>
> The problem is that this would require that I rewrite many queries to
> utilize the function... I'm currently using smallint to store the bools
> because I want 0/1 as output. I'd like to move away from this but in php
> "f" evaluates true!
>
> I have followed a good deal of discussion regarding SET DATESTYLE and I
> really was hoping that there was some way to SET BOOLEANSTYLE or something
> like this.
>
> Unfortunately, I find both the case statement and the sql function to be
> overkill... My thinking is why should it require more work to deal with the
> simplest of all data types... I very much still appreciate the response. :)
>
> I think I am ultimately making a case for a new feature in a future version
> of postgres. This is also important for people who want to port MySQL
> applications to PostgreSQL! As a committed postgres advocate, I never want
> to say we can't get there from here... (MySQL outputs bools as 1/0 if I am
> not mistaken, though I haven't used it in some time!)
>
> I regret I am not a real hacker or I'd work on this myself!
>
> Best Regards,
> Garo.
>
> on 10/4/02 6:36 PM, Jeff Davis at list-pgsql-general@empires.org wrote:
> > The best way change the output style in general is a stored procedure.
> > However, with booleans it's simple enough that you could write it out
> > inline if you want:
> > -------------------------------------------------------------------------
> >----- jdavis=> create table b(a bool);
> > CREATE
> > jdavis=> insert into b values('t');
> > INSERT 67682 1
> > jdavis=> insert into b values('f');
> > INSERT 67683 1
> > jdavis=> select case when a then 'YES' else 'NO' end from b ;
> > case
> > ------
> > YES
> > NO
> > (2 rows)
> > -------------------------------------------------------------------------
> >----- --
> >
> > The case statement basically just special cases the two values. In this
> > case it of course changes 't' to 'YES' and 'f' to 'NO'.
> >
> > You could also make a SQL function out of it no problem:
> >
> > jdavis=> create function myfn(bool) returns text as 'select case when $1
> > then ''YES'' else ''NO'' end;' language 'sql';
> >
> > Then just use that in your selects:
> > jdavis=> select myfn(a) from b;
> > myfn
> > ------
> > YES
> > NO
> > (2 rows)
> >
> >
> > Regards,
> > Jeff Davis
> >
> > On Friday 04 October 2002 06:17 pm, Garo Hussenjian wrote:
> >> A friend of mine has told me that using the Zope pgsql driver you can
> >> set the output format of postgres booleans...
> >>
> >> Unfortunately, I'm using php and would like to do this also.
> >>
> >> Is the zope driver doing this or is it some sort of option that can be
> >> sent when the connection is made or a query that can be run?
> >>
> >> Thanks,
> >> Garo.
> >>
> >>
> >> =-=-==-=-=-==
> >>
> >> Xapnet Internet Solutions
> >> 1501 Powell St., Suite N
> >> Emeryville, CA 94608
> >>
> >> Tel - (510) 655-9771
> >> Fax - (510) 655-9775
> >> Web - http://www.xapnet.com
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 2: you can get off all lists at once with the unregister command
> >>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> =-=-==-=-=-==
>
> Xapnet Internet Solutions
> 1501 Powell St., Suite N
> Emeryville, CA 94608
>
> Tel - (510) 655-9771
> Fax - (510) 655-9775
> Web - http://www.xapnet.com
>
>
> ---------------------------(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