Re: Boolean output format - Mailing list pgsql-general

>
> Some database abstraction layers provide a consistent query interface to a
> database, like exec() or query(), and you pass in the sql... In our case,
> our abstraction layer also generates much of the sql, so certain queries
> that are very different in MySQL and PgSQL are the same in my code, making
> my applications almost 100% DB-independant. This is the case for my
> databases that use smallint, but I'd like to use "real" booleans without
> loosing the independence.
>
> An example: I get a MySQL db with real smallints and booleans, I translate
> to pgsql and now the bools and original smallints are non-distinguishable!
> I've lost something in the translation, or alternatively, I don't go to the
> smallint and booleans don't evaluate! I'll use smallint for now.

Ah, I think I finally get it. You go MySQL->PostgreSQL->MySQL and end up with
something a little different. Reminds me of some stories I've heard about
online text translators like Babelfish :)

That's certainly a good point.

>
> As for the MySQL thing, it uses 1/0 and as a result remains consistent with
> the php convention for booleans. I guess that's just the trade-off when
> using a dynamically typed language... I suppose I think of postgresql as
> the final destination for my data, and I'd like to think that the
> language/interface that I choose doesn't really matter. Regardless of the
> interface, I'd like to "communicate" with postgres directly if possible!

I don't think you can really get any responses from postgres except in the
form of a NUL-terminated string. I suppose you could store binary data in a
bytea attribute, but that seems like a lot of work. You can't just bring a
byte from the internal data representation of the type into your application
directly.

> BTW/ I'm sort of packing two years of working with both databases and
> hitting various snafus all in one discussion. Ultimately, there are many
> reason's I'd like this feature, but it just boils down to the fact that php
> and postgres are great together, except for boolean! It seems you really do
> understand my frustration.
>

Yup, I have been frustrated by very similar problems. I'm a PHP programmer as
well :)

>
> I read Tom Lane's post and it seems that backward compatibility is the
> primary obstacle... This is something seemingly simple enough to solve, but
> I don't claim to understand even 5% of the core developer's job. I don't
> even full understand where the responsibility of the database ends that of
> the adapters begin. I'm happy to have just gotten the responses and some
> additional understanding. I hope that this conversation does continue on
> until the idea is
>

There's always a little overlap: should the application do the work or the
database? I hope whatever it is works out, and in a backwards-compatible way.

Oh, and you might take a look into using the system catalogs to determine
attribute types in your app. That would at least make it possible for you to
know how to convert.

Well, I'm certainly good at coming up with almost-solutions, aren't I? :)

Regards,
    Jeff Davis