Thread: bit field changes in 7.2.1

I'm on a Linux RH 7.2 system, which came with Pg 7.1.2 (I think).  When 
there, I prototyped some code that worked well, and looked like:

create table ref_sp
(  name varchar(10),  sname char(1),  bitmask bit(6)
);

insert into ref_sp values ('one',       '1', b'000001');
insert into ref_sp values ('two',       '2', b'000010');
insert into ref_sp values ('four',      '4', b'000100');
insert into ref_sp values ('eight',     '8', b'001000');
insert into ref_sp values ('sixteen',   's', b'010000');
insert into ref_sp values ('thirtytwo', 't', b'100000');

create table emp
(  id int,  name varchar(30),  specialties bit(6)
);

insert into emp values (1, 'mary_124', b'000111');
insert into emp values (2, 'joe_14st', b'110101');

Which allowed me to find out who had what bit (specialty) set with:

select sp.name
from emp s, ref_sp sp
where s.specialties & sp.bitmask != b'0'::bit(6)  and s.name = 'joe_14st' ;

EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon).  It 
forces me to use "b'000000'" instead of "b'0'::bit(6)".  Searching thru 
the docs, I find a note that says:

---
Note:  Prior to PostgreSQL 7.2, BIT type data was zero-padded on the 
right. This was changed to comply with the SQL standard. To implement 
zero-padded bit strings, a combination of the concatenation operator and 
the substring function can be used.
---

Obviously the source of my problem.  However, whoever wrote that note 
didn't say how to do it (examples are *SO* useful), and I can't imagine 
the solution.

* Can anyone clue me into how I can do that, in SELECT / INSERT / UPDATE 
statements?
* Or must I resort to doing the equivalent in Perl as I create the SQL?
* Or is there a backward-compatibility flag?

Thanks!
Kevin

Kevin Brannen writes:

> EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon).  It
> forces me to use "b'000000'" instead of "b'0'::bit(6)".

Which is a problem why?

> Searching thru the docs, I find a note that says:
>
> ---
> Note:  Prior to PostgreSQL 7.2, BIT type data was zero-padded on the
> right. This was changed to comply with the SQL standard. To implement
> zero-padded bit strings, a combination of the concatenation operator and
> the substring function can be used.
> ---
>
> Obviously the source of my problem.  However, whoever wrote that note
> didn't say how to do it (examples are *SO* useful), and I can't imagine
> the solution.

In your case the solution is to type the six zeroes.

The comment referred to cases where the results of computations needed to
be forced to the right length, in which case you could use something like
     substring(computation() || b'000000' for 6)

The question whether the constant should go before or after the
computation, and whether it should be zeros or ones is a matter of taste,
which is why an example has been omitted.

-- 
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut wrote:
> Kevin Brannen writes:
> 
> 
>>EXCEPT that now fails in 7.2.1 (I just upgraded this afternoon).  It
>>forces me to use "b'000000'" instead of "b'0'::bit(6)".
> 
> 
> Which is a problem why?

Because in the real system, it will be b'0'::bit(64) or b'001::bit(64) 
or etc.  A bit nasty to type isn't it. :-)  And of course that's for 1 
table, in another place it be 40 bits wide, and in yet a third 96 bits 
wide.  So a simple mechanism to make it the correct size (for 
comparisons) is very useful (to maintain my sanity. :-)

...
> 
> 
> In your case the solution is to type the six zeroes.
> 
> The comment referred to cases where the results of computations needed to
> be forced to the right length, in which case you could use something like
> 
>       substring(computation() || b'000000' for 6)
> 
> The question whether the constant should go before or after the
> computation, and whether it should be zeros or ones is a matter of taste,
> which is why an example has been omitted.
> 

I'm still not sure I see the problem, but that doesn't really matter. 
If that's the explanation, then *that* should have been included with 
the note, IMO.  OTOH, I still don't see where that helps me.  How does 
it give me a string of bits like b'0'::64 does, or b'1'::64 does?  And 
something like:

select substring(1 || b'000000' for 6);

assuming some computation returns a "1", tells me:

ERROR:  Unable to identify an operator '||' for types 'integer' and 
'bit'   You will have to retype this query using an explicit cast


In an email, Chris suggested using rpad(), which looks good but doesn't 
quite work, unless I'm overlooking something (if so, pleeeeease point it 
out!).  When I try it in my query I get:

select ... from ...
where sp.bitmask != cast(rpad('0',6,'0') as bit(6));

ERROR:  Cannot cast type 'text' to 'bit'


<whine>
I guess my issue is that when some feature is taken out, there needs to 
be another way to do it, AND it needs to be documented.  Or just leave 
it in and flag it as an extension.
</whine> ;-)

I guess I'll just have to read the bits in from the DB reference table, 
then store and manipulate them all in Perl...sigh...

Thanks for the effort though!
Kevin