Postgres Pro
Downloads
Home   >   mailing lists

Thread: Regarding BITs vs. INTs

Regarding BITs vs. INTs

From
Bill Moran
Date:
I hadn't really looked at this until I started having problems with it.

For those who haven't been following along, I'm converting an application
originally written in MSSQL to Postgres.

I'm a little startled by how BIT fields are handled differently.  Apparently,
MSSQL converts freely between BIT and INT.  Those who know, already know that
Postgres doesn't do this.

On one hand, I'm curious as to why.  It would seem easy enough to me.  Although
there _is_ some abiguity (for example, if I use the statement "bit_value := 11",
does it store binary 11 in bit_value, or binary 1011?  I guess there doesn't
need to be any more reason than that, huh?)

As I look over the handling of BIT fields and consider all the places in the code
that will need tweaked, I'm thinking the path of least resistance is to simply
replace all BITs with INTs (or maybe TINYINTs).  Aside from the obvious storage
space issue, can anyone think of a reason that this would be a bad idea?
Actually, the storage space isn't much of an issue in this case, as most of the
BITs are return values from functions.

I know, these shoud be BOOLEAN, but it'll take more work to fix if I convert
them to BOOLEAN than if I convert them to INTs.  For example:

CREATE FUNCTION bit_function(DATE)
RETURNS BIT
AS ' ...

CREATE FUNCTION some_other_function()
RETURNS bla_bla_bla
AS '

    SELECT
        CASE bit_function(sometable.somedatefield)
         WHEN 1 THEN ''bit_function was true''
         ELSE ''bit_function was false''
        END,
        ...

Obviously, it's more work to convert bit_function() to BOOLEAN and fix all
the places it's used than it would be to convert bit_function to INT and
have all the places it's used just start working.

But I'm wondering if anyone sees any gotchas?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Re: Regarding BITs vs. INTs

From
Stephan Szabo
Date:
On Fri, 27 Feb 2004, Bill Moran wrote:

> I hadn't really looked at this until I started having problems with it.
>
> For those who haven't been following along, I'm converting an application
> originally written in MSSQL to Postgres.
>
> I'm a little startled by how BIT fields are handled differently.  Apparently,
> MSSQL converts freely between BIT and INT.  Those who know, already know that
> Postgres doesn't do this.

No, but IIRC, it does allow casts between them, it just requires that you
explicitly mark that you want to cast the value.  If you really want to,
you could consider changing those casts into implicit casts and see if
that does what you want.

Re: Regarding BITs vs. INTs

From
Bill Moran
Date:
Stephan Szabo wrote:
> On Fri, 27 Feb 2004, Bill Moran wrote:
>
>>I hadn't really looked at this until I started having problems with it.
>>
>>For those who haven't been following along, I'm converting an application
>>originally written in MSSQL to Postgres.
>>
>>I'm a little startled by how BIT fields are handled differently.  Apparently,
>>MSSQL converts freely between BIT and INT.  Those who know, already know that
>>Postgres doesn't do this.
>
> No, but IIRC, it does allow casts between them, it just requires that you
> explicitly mark that you want to cast the value.  If you really want to,
> you could consider changing those casts into implicit casts and see if
> that does what you want.

True, and originally that's what I was doing to fix it.  For example:

CASE bir_returning_function() WHEN 1 THEN ...

was being changed to:

CASE bit_returning_function() WHEN 1::BIT THEN ...

But, the reason I've stopped to reconsider is the fact that it will take a lot
longer to change all the places that bit_returning_function() is used than it
will to just convert big_returing_function() to return an INT.  Some of these
functions are used 20 or 30 different places.

Thanks for the feedback

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Re: Regarding BITs vs. INTs

From
Stephan Szabo
Date:
On Fri, 27 Feb 2004, Bill Moran wrote:

> Stephan Szabo wrote:
> > On Fri, 27 Feb 2004, Bill Moran wrote:
> >
> >>I hadn't really looked at this until I started having problems with it.
> >>
> >>For those who haven't been following along, I'm converting an application
> >>originally written in MSSQL to Postgres.
> >>
> >>I'm a little startled by how BIT fields are handled differently.  Apparently,
> >>MSSQL converts freely between BIT and INT.  Those who know, already know that
> >>Postgres doesn't do this.
> >
> > No, but IIRC, it does allow casts between them, it just requires that you
> > explicitly mark that you want to cast the value.  If you really want to,
> > you could consider changing those casts into implicit casts and see if
> > that does what you want.
>
> True, and originally that's what I was doing to fix it.  For example:

No, I meant change the rows in pg_cast for the casts to mark the cast as
implicit rather than explicit (castcontext='i' rather than
castcontext='e').  Then the cast should happen automatically when
appropriate rather than requiring an explicit cast.

Re: Regarding BITs vs. INTs

From
Bill Moran
Date:
Stephan Szabo wrote:
> On Fri, 27 Feb 2004, Bill Moran wrote:
>
>>Stephan Szabo wrote:
>>
>>>On Fri, 27 Feb 2004, Bill Moran wrote:
>>>
>>>>I hadn't really looked at this until I started having problems with it.
>>>>
>>>>For those who haven't been following along, I'm converting an application
>>>>originally written in MSSQL to Postgres.
>>>>
>>>>I'm a little startled by how BIT fields are handled differently.  Apparently,
>>>>MSSQL converts freely between BIT and INT.  Those who know, already know that
>>>>Postgres doesn't do this.
>>>
>>>No, but IIRC, it does allow casts between them, it just requires that you
>>>explicitly mark that you want to cast the value.  If you really want to,
>>>you could consider changing those casts into implicit casts and see if
>>>that does what you want.
>>
>>True, and originally that's what I was doing to fix it.  For example:
>
> No, I meant change the rows in pg_cast for the casts to mark the cast as
> implicit rather than explicit (castcontext='i' rather than
> castcontext='e').  Then the cast should happen automatically when
> appropriate rather than requiring an explicit cast.

OH!  This is really neat, I didn't know this could be done!

I'll look into this, but it sure looks like this is going to be REALLY
helpful.  If this works out, Stephan, you'll go on my list of people who,
"If you're ever in the Pittsburgh area, the beer's on me!"

--
Bill Moran
Potential Technologies
http://www.potentialtech.com