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
