Thread: bit operations
I'm new to PostgreSQL, and I'm trying to migrate a MySQL-implementation to this instead... It's really different from MySQL,and the docs are not as good, and the IRC-channel (on EFNet at least) is really really inactive. But still. :-) Maybethe mailinglist is more impressive... Have a little problem with bit operaitions that I cannot find the answer to on the Internet. Have been searching throughthe archives but no result. In MySQL you can have a INT column and do bit logic ala C-style like this: "select * from table where flags & 4;" Can I do something similar with std SQL? I've been trying to cast everything to BIT but without success, and I've also beencreating a "flags bit(4)", setting a row to "1000" (8) and trying to compare, but I have absolutely no idea how to. Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?! Please help me! :-) --- Johan Björk
Johan Björk wrote: > > I'm new to PostgreSQL, and I'm trying to migrate a MySQL-implementation to this instead... It's really different from MySQL,and the docs are not as good, and the IRC-channel (on EFNet at least) is really really inactive. But still. :-) Maybethe mailinglist is more impressive... > > Have a little problem with bit operaitions that I cannot find the answer to on the Internet. Have been searching throughthe archives but no result. > > In MySQL you can have a INT column and do bit logic ala C-style like this: > "select * from table where flags & 4;" > > Can I do something similar with std SQL? I've been trying to cast everything to BIT but without success, and I've alsobeen creating a "flags bit(4)", setting a row to "1000" (8) and trying to compare, but I have absolutely no idea howto. > > Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?! select * from table where (flags & 8) <> 0; select * from table where (flags & 2) <> 0; Regards, Oliver
Johan Björk wrote: > > I'm new to PostgreSQL, and I'm trying to migrate a MySQL-implementation to this instead... It's really different from MySQL,and the docs are not as good, and the IRC-channel (on EFNet at least) is really really inactive. But still. :-) Maybethe mailinglist is more impressive... > > Have a little problem with bit operaitions that I cannot find the answer to on the Internet. Have been searching throughthe archives but no result. > > In MySQL you can have a INT column and do bit logic ala C-style like this: > "select * from table where flags & 4;" But in PostgreSQL WHERE clause must return type bool, not type int4 > > Can I do something similar with std SQL? I've been trying to cast everything to BIT but without success, and I've alsobeen creating a "flags bit(4)", setting a row to "1000" (8) and trying to compare, but I have absolutely no idea howto. > > Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?! Sorry, I didn't read the whole question first: select * from table where ((flags & 8) <> 0) and ((flags & 2) <> 0); Regards, Oliver
From: "Johan Björk" <johan@websidorna.com> > Have a little problem with bit operaitions that I cannot find the answer to on the Internet. Have been searching through the archives but no result. > > In MySQL you can have a INT column and do bit logic ala C-style like this: > "select * from table where flags & 4;" If you're using an int for "flags": select * from table where (flags & 4) > 0; The bitwise AND returns an integer rather than a boolean, and AFAIK SQL doesn't follow C's rules on this. > Can I do something similar with std SQL? I've been trying to cast everything to BIT but without success, and I've also been creating a "flags bit(4)", setting a row to "1000" (8) and trying to compare, but I have absolutely no idea how to. > > Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?! To check two values just do: SELECT * FROM table WHERE (flags & val1 & val2) > 0; If you want to use BIT types you'll need something like: SELECT * FROM table WHERE (flags & '0010100'::BIT) <> '0'::BIT; and updates like UPDATE table SET flags = flags | '0001000'::BIT; Note the need to have the same string-length when using AND/OR. HTH - Richard Huxton
>>>>> "Oliver" == Oliver Vecernik <vecernik@aon.at> writes: >> Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?! Oliver> Sorry, I didn't read the whole question first: Oliver> select * from table where ((flags & 8) <> 0) and ((flags & 2) <> 0); In the old days, we spelled that as flags & 10 = 10. :) Wouldn't that work just as well? -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
"Randal L. Schwartz" wrote: > > >>>>> "Oliver" == Oliver Vecernik <vecernik@aon.at> writes: > > >> Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?! > > Oliver> Sorry, I didn't read the whole question first: > > Oliver> select * from table where ((flags & 8) <> 0) and ((flags & 2) <> 0); > > In the old days, we spelled that as flags & 10 = 10. :) Wouldn't that > work just as well? As far as I know testing for <> 0 could be calculated faster. Does anybody know if this is true? Regards, Oliver
>>>>> "Oliver" == Oliver Vecernik <vecernik@aon.at> writes: Oliver> "Randal L. Schwartz" wrote: >> >> >>>>> "Oliver" == Oliver Vecernik <vecernik@aon.at> writes: >> >> >> Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?! >> Oliver> Sorry, I didn't read the whole question first: >> Oliver> select * from table where ((flags & 8) <> 0) and ((flags & 2) <> 0); >> >> In the old days, we spelled that as flags & 10 = 10. :) Wouldn't that >> work just as well? Oliver> As far as I know testing for <> 0 could be calculated faster. Does Oliver> anybody know if this is true? But that's wrong for flags & 10. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
On Wed, Jul 04, 2001 at 02:56:06PM +0100, Richard Huxton wrote:
> From: "Johan Björk" <johan@websidorna.com>
>
> > Have a little problem with bit operaitions that I cannot find the answer
> to on the Internet. Have been searching through the archives but no result.
> >
> > In MySQL you can have a INT column and do bit logic ala C-style like this:
> > "select * from table where flags & 4;"
>
> If you're using an int for "flags":
>
> select * from table where (flags & 4) > 0;
>
> The bitwise AND returns an integer rather than a boolean, and AFAIK SQL
> doesn't follow C's rules on this.
>
> > Can I do something similar with std SQL? I've been trying to cast
> everything to BIT but without success, and I've also been creating a "flags
> bit(4)", setting a row to "1000" (8) and trying to compare, but I have
> absolutely no idea how to.
> >
> > Say I wanna check if 8 (1xxx) and 2 (xx1x) is set, how do I do that?!
>
> To check two values just do:
>
> SELECT * FROM table WHERE (flags & val1 & val2) > 0;
i don't think so.
val1 := 8 := 01000
val2 := 2 := 00010
val1 & val2 00000 zero
probably you meant
(flags & val1) > 0 and (flags & val2) > 0
or
flags & (val1 + val2) > 0
> If you want to use BIT types you'll need something like:
>
> SELECT * FROM table WHERE (flags & '0010100'::BIT) <> '0'::BIT;
>
> and updates like
>
> UPDATE table SET flags = flags | '0001000'::BIT;
>
> Note the need to have the same string-length when using AND/OR.
cool. thanks.
--
I'd concentrate on "living in the now" because it is fun
and on building a better world because it is possible.
- Tod Steward
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!