CODEWITHSUNDEEP
sqloracle-databasepostgresql
htf
htf

Reputation: 143

Is there an equivalent to Oracle's BITAND() function in Postgres?

I know there is a Postgres function BIT_AND(), which is not the same thing - in fact it is the opposite of my end goal. I would like to be able to decode the results of an operation like Postgres BIT_AND() back into the original bits. I have code that does this in Oracle, like so:

select NVL(DECODE(BITAND(foo.bar, POWER (2, 1)), POWER (2, 1), 1), 0) first_bit,
NVL(DECODE(BITAND(foo.bar, POWER (2, 2)), POWER (2, 2), 1), 0)  second_bit
from 
(select 1234 bar from dual
union select 12345 bar from dual) foo

If the first bit of foo.bar is set, first_bit would return 1, otherwise 0, etc. I can translate everything to Postgres except BITAND(), and the only thing I've found discussing the issue online is a thread on forums.devshed.com from 2010 with zero responses - appreciate any insight.

EDIT: Answered below, thank you! Here are the tweaks I needed to make on my side to make it work, all because I'm actually dealing with up to 43 options that are coded as bits, and I really want to be able to use the power() function to make it very clear which bit I'm going for, so I had to convert data types.

select ((foo.bar & power(2,1)::bigint) > 0)::int as first_bit,
    ((foo.bar & power(2,43)::bigint) > 0)::int as forty_third_bit
from (select 1031 as bar union all
    select 8796160131072 
    ) foo

Upvotes: 2

Views: 6060

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

In Postgres, the bitwise and operator is &.

You would seem to want something like this:

select ((foo.bar & 2) > 0)::int as first_bit,
       ((foo.bar & 4) > 0)::int as second_bit
from (select 1234 as bar union all
      select 12345
     ) foo;

I'm not sure why you are counting the bits from the second one, but the operator does the same thing as the Oracle function.

Here is a SQL Fiddle that better illustrates the operator.

Upvotes: 3

Related Questions