bit field changes in 7.2.1

From: Kevin Brannen <kevinb(at)nurseamerica(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: bit field changes in 7.2.1
Date: 2002-07-03 22:16:06
Message-ID: 3D2377A6.3050006@nurseamerica.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jie Liang 2002-07-03 22:45:25 postgres7.2.1 upgrading
Previous Message Mark Frazer 2002-07-03 21:12:32 Re: Possible Bug regarding temp tables (sql or psql?)