Skip site navigation (1) Skip section navigation (2)

Regarding BITs vs. INTs

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Regarding BITs vs. INTs
Date: 2004-02-28 00:47:53
Message-ID: 403FE539.7000509@potentialtech.com (view raw or flat)
Thread:
Lists: pgsql-general
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


Responses

pgsql-general by date

Next:From: Jim WilsonDate: 2004-02-28 00:54:09
Subject: Re: Restoring a table with a different name
Previous:From: Chris BrowneDate: 2004-02-28 00:18:00
Subject: Re: efficient storing of urls

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group