Re: Boolean output format

on 10/4/02 7:54 PM, Jeff Davis at list-pgsql-general(at)empires(dot)org wrote:

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

You are very kind. :)

> 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)));

Interesting... It's looks and acts like a boolean, it's just not a boolean!
I like it though it's not a native solution.

>
> 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.

I understand. I can implement a wrapper to convert bools, but I'd have to
store the schema in my application. Using php, the database is the only
place where types are actually known! I should check to see if Pear's DB
object does any type-casting at run-time.

> 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).

I have thought about this as well. The user-defined types are extremely
useful but they add additional (in this case undesirable) complexity. If we
were talking about anything other than booleans, I would not be making this
case in the first place.

>
> 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.
>
Is this because of processing overhead or other reasons? I'd argue that the
boolean is an extremely important type, and just as (if not more) common as
date and datetime, and it's certainly less complicated.

> 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?
>
Now you've hit the root of the problem! I want to be able to forward and
reverse engineer postgres and mysql schemas in php for a basic set of
datatypes (varchar, int, decimal, boolean, date, datetime, and maybe a
couple others). The problem with smallint is that I can translate boolean to
smallint, but it doesn't work in reverse! This is mostly an interoperability
concern.

I have a database abstraction layer in my application that further
generalizes my queries with some sql abstration... Right now the only
difference between postgres and mysql is the return result of a native
boolean.

> 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.
>

I can understand the developers' desire for efficiency in the core. I should
always be weighed against user needs. The answer, in this case, is unlikely
to be a true or false... I'd never expect that the world could be as precise
as our favorite database!

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

I understand and I greatly appreciate your feedback. I wonder if any of the
developers might express their opinions on this. I'd love know what they
think.

Regards,
Garo.

>
> 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(at)empires(dot)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(at)postgresql(dot)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(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

=-=-==-=-=-==

Xapnet Internet Solutions
1501 Powell St., Suite N
Emeryville, CA 94608

Tel - (510) 655-9771
Fax - (510) 655-9775
Web - http://www.xapnet.com