bit operations

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. :-) Maybe the mailinglist is more impressive...

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;"

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?!

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. :-) Maybe the mailinglist is more impressive...

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;"

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 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?!

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/&gt;
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/&gt;
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!