Re: getting postgres to emulate mysql/sqlserver bit datatype

Hi,
I am trying to port an app to postgres and have come up against a most
annoying problem. The app works with both mysql and sqlserver, who
both seem to have a bit datatype instead of a proper boolean like pg.
Alas, pg won't accept 1 and 0 for boolean... and npgsql won't convert
my numeric(1) into a boolean (and shouldn't either!). Is there any way
to force pg to accept 1 and 0 for boolean? I know it probably isn't
optimal, but hey. If there is a standard way to do this (and no,
rewriting is not an option) I'm all ears.
Cheers
Antoine

Anton Melser wrote:
> Is there any way to force pg to accept 1 and 0 for boolean?

You can tweak the context for the cast between int and boolean. Read up
about the pg_cast system catalog.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Anton Melser wrote:
> Hi,
> I am trying to port an app to postgres and have come up against a most
> annoying problem. The app works with both mysql and sqlserver, who
> both seem to have a bit datatype instead of a proper boolean like pg.
> Alas, pg won't accept 1 and 0 for boolean... and npgsql won't convert
> my numeric(1) into a boolean (and shouldn't either!). Is there any way
> to force pg to accept 1 and 0 for boolean? I know it probably isn't
> optimal, but hey. If there is a standard way to do this (and no,
> rewriting is not an option) I'm all ears.
> Cheers
> Antoine

postgres=# insert into bool_test values(1::boolean);
INSERT 166968558 1
postgres=# insert into bool_test values(0::boolean);
INSERT 166968559 1
postgres=# select * from bool_test ;
one
-----
t
f
(2 rows)

postgres=#

postgres=# insert into bool_test values(cast(0 as boolean));
INSERT 166968560 1
postgres=# insert into bool_test values(cast(1 as boolean));
INSERT 166968561 1
postgres=# select * from bool_test ;
one
-----
t
f
f
t
(4 rows)

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

"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?

regards, tom lane

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

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

Shane Ambler <pgsql(at)007Marketing(dot)com> writes:
> 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.

I think actually what he needs is what Peter suggested upthread, namely
to weaken the context-restriction on the int-to-bool cast. For a
comparison like
WHERE boolcol = 1
the system is still gonna see that as a bool vs int comparison, and it
won't take it unless int-to-bool is an implicit cast.

Another route might be to create a bool = int operator. That seems less
likely to break expected behaviors, but it'd be more work.

regards, tom lane

On Mon, 2007-02-12 at 09:09 +0100, Anton Melser wrote:
> 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.

Can you just do something simple like:

CREATE DOMAIN intbool AS integer CHECK (VALUE IN (0,1));

and convert the data type to that?

Then, can you tell npgsql to map the database's intbool type to the
application's bool type?

Regards,
Jeff Davis

> I think actually what he needs is what Peter suggested upthread, namely
> to weaken the context-restriction on the int-to-bool cast.

Indeed... Peter's suggestion seems to have solved all my problems. So
even though it probably shows just how embarrassingly bad my sql is...

update pg_cast set castcontext = 'i'
where (castsource = (select oid from pg_type where typname = 'bool')
and casttarget = (select oid from pg_type where typname = 'int4'))
or (castsource = (select oid from pg_type where typname = 'int4')
and casttarget = (select oid from pg_type where typname = 'bool'))

For the archives.
Thanks to everyone.
Anton
ps. This is probably only for situations where it is absolutely
necessary... but I am now passing my nunit tests! :-)

On 12/02/07, Anton Melser <melser(dot)anton(at)gmail(dot)com> wrote:
> > I think actually what he needs is what Peter suggested upthread, namely
> > to weaken the context-restriction on the int-to-bool cast.
>
> Indeed... Peter's suggestion seems to have solved all my problems. So
> even though it probably shows just how embarrassingly bad my sql is...

I spoke too soon!

select executor_id, is_dedicated, is_connected, ping_time, host, port,
usr_name,
cpu_max, cpu_usage, cpu_avail, cpu_totalusage, mem_max, disk_max,
num_cpus, os, arch
from executor where is_dedicated = 1 and is_connected = 1

ERROR: operator is not unique: boolean = integer
État SQL :42725
Astuce : Could not choose a best candidate operator. You may need to
add explicit type casts.
Caractère : 201

I get this whether castcontext is 'a' or 'i'. I am so close to having
this fixed!!! Any ideas most welcome...
Cheers
Anton

"Anton Melser" <melser(dot)anton(at)gmail(dot)com> writes:
> ERROR: operator is not unique: boolean = integer

> I get this whether castcontext is 'a' or 'i'.

If you make both cast directions the same priority then the system has
no basis for choosing bool = bool over int = int or vice versa. Try
making one direction 'i' and the other not. I'm not sure which one
ought to be 'i', really --- it depends a lot on the details of the
queries you are trying to make work.

regards, tom lane

On 13/02/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Anton Melser" <melser(dot)anton(at)gmail(dot)com> writes:
> > ERROR: operator is not unique: boolean = integer
>
> > I get this whether castcontext is 'a' or 'i'.
>
> If you make both cast directions the same priority then the system has
> no basis for choosing bool = bool over int = int or vice versa. Try
> making one direction 'i' and the other not. I'm not sure which one
> ought to be 'i', really --- it depends a lot on the details of the
> queries you are trying to make work.

That did the trick. Both seem to work, so for me that's great. Thanks
heaps. I may end up trying to get them to change it from 1 and 0 to
'1' and '0', as sql server accepts it, and if sql server accepts it, I
would be surprised if mysql doesn't...
Thanks again,
Anton