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

Re: bit datatype and getObject()

From: Kris Jurka <books(at)ejurka(dot)com>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: bit datatype and getObject()
Date: 2010-12-22 19:24:57
Message-ID: alpine.BSO.2.00.1012221358080.19025@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

On Wed, 24 Nov 2010, Kris Jurka wrote:

> On Tue, 23 Nov 2010, Thomas Kellerer wrote:
>
>> the JDBC driver seems to map columns defined as "bit" to Boolean regardless 
>> of the definition of the column. So even a bit(5) is returned as boolean 
>> value which I think is not correct.
>> 
>
> Yeah, losing data is not good.  Previously the discussion on list was to use 
> java.util.BitSet, but it never resulted in a patch.
>

I've looked at this in some more detail and I don't think BitSet matches 
up closely enough with bit(n)/varbit to make it work.  BitSet does not 
store the total bit string length, only the length until the highest set 
bit.  So if you have a BitSet object you can make a safe roundtrip to the 
database and back, but you can't make a safe roundtrip from the database 
to a BitSet and back.  If you have a bit/varbit field that has a zero 
in the most significant bit, it will not go back to the database the same.

This causes some problems on the database side, for example:

CREATE TEMP TABLE bittest (a bit(3));
INSERT INTO bittest VALUES ('010');

Once you pull this into a BitSet object you can't reconstruct the original 
and you are left with an error like this for bit types:

UPDATE bittest SET a = '10'::varbit;
ERROR:  bit string length 2 does not match type bit(3)

For varbit strings you can store the new value, but then later operations 
may fail:

CREATE TEMP TABLE varbittest(a varbit(3), b varbit(3));
INSERT INTO varbittest VALUES ('010', '111');
SELECT a & b FROM varbittest;
UPDATE varbittest SET a = '10'::varbit;
SELECT a & b FROM varbittest;
ERROR:  cannot AND bit strings of different sizes

So if the problem was that tons of people on the Java side had BitSets 
they wanted to put into the database and retrieve, this might be 
tolerable.  That's not our problem though, we're really looking at the 
database side of things and trying to represent that in Java, so we need 
another approach.  At the moment, returing a String seems easiest, do 
other people have ideas?

Kris Jurka

In response to

Responses

pgsql-jdbc by date

Next:From: Kevin GrittnerDate: 2010-12-22 19:35:31
Subject: Re: bit datatype and getObject()
Previous:From: Thomas KellererDate: 2010-12-22 17:14:30
Subject: Re: Bug with DatabaseMetaData and temporary tables/schemas

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