Regarding BITs vs. INTs

[Bill Moran]

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(DA TE)
RETURNS BIT
AS ' ...

CREATE FUNCTION some_other_func tion()
RETURNS bla_bla_bla
AS '

SELECT
CASE bit_function(so metable.somedat efield)
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bill Moran]

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_f unction() WHEN 1 THEN ...

was being changed to:

CASE bit_returning_f unction() 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_f unction() is used than it
will to just convert big_returing_fu nction() 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Bill Moran]

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
originall y 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)