From: Kevin Brannen <kevinb@nurseamerica.net>
To: pgsql-sql@postgresql.org
Subject: bit field changes in 7.2.1
Date: Wed, 03 Jul 2002 17:16:06 -0500
Message-ID: <3D2377A6.3050006@nurseamerica.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kevinb@nurseamerica.net
Subject: Re: bit field changes in 7.2.1
In-Reply-To: <3D2377A6.3050006@nurseamerica.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox