By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,362 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,362 IT Pros & Developers. It's quick & easy.

Regarding BITs vs. INTs

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
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

Nov 23 '05 #2

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3

P: n/a

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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.