Re: getting postgres to emulate mysql/sqlserver bit datatype

Anton Melser wrote:
> On 12/02/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> > Anton Melser wrote:
>> >> Is there any way
>> >> to force pg to accept 1 and 0 for boolean?
>>
>> > postgres=# insert into bool_test values(1::boolean);
>> > INSERT 166968558 1
>> > postgres=# insert into bool_test values(0::boolean);
>> > INSERT 166968559 1
>>
>> Possibly Anton is using an old version in which there wasn't a built in
>> int-to-bool cast?
>
> In my searching I did turn up a comment (maybe from you even!) about
> how it wouldn't work (before at least). I guess my problem is that
> there is a body of sql that can't be changed, or at least the other
> devs aren't interested enough in pg support to let me add a ton of if
> pg else code. I think that creating a type is probably the way to go,
> though if anyone has any advice I'm all ears.
> I have .net code which has things like
> bool myBool = datareader.GetBoolean(datareader.GetOrdinal("my_bool"));
> Or something similar (I'm at work...). So I need to be able for npgsql
> to return a boolean, but also need to be able to insert and compare
> with straight 1, 0. I suppose there is a way that I can get around it
> but after a couple of hours I haven't been able to come up with
> anything.
> Cheers
> Anton
>

I haven't tried this myself - you will want to look into the pg_catalog
data, this is where postgres gets it's information on how to handle
various tasks.

If you look at the bool type you will find it calls a function called
boolout(bool) which returns a cstring to return the data for the boolean
- you could replace this with your own function that returns a 1 or 0
instead of true or false. Similarly the functions to input/compare etc
can be changed/replaced.

A pg_dumpall should bring these changes across when upgrading.

The other option would be to change the internal functions used before
you compile your own copy of postgres and to repeat these mods in any
upgrades.

Otherwise you would need to change your client coding to work with
0/1 as well as true/false.

--

Shane Ambler
pgSQL(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz