Mailbox-Line: From kevinb@nurseamerica.net  Wed Jul  3 19:59:51 2002
Message-ID: <3D2377A6.3050006@nurseamerica.net>
Date: Wed, 03 Jul 2002 17:16:06 -0500
From: Kevin Brannen <kevinb@nurseamerica.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020529
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: bit field changes in 7.2.1
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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