CODEWITHSUNDEEP
sqlpostgresqlbitwise-operators
Will
Will

Reputation: 49

Bitwise Operator Postgres

I have a table that contains binary values such as: 

binaryid    description
1           description1
2           description2
4           description3
8           description4

And I have another table that contains values:

DBID BinaryTogether
1    15
1    12
1     6

With the bitwise operator I want to get a table that would contain the following:

DBID   BinaryTogether BitwiseResult
1      15             description1,description2,description3,description4
1      12             description3,description4
1       6             description2, description3

Upvotes: 1

Views: 221

Answers (1)

Abelisto
Abelisto

Reputation: 15614

You can join those tables using & bitwise operator and then aggregate descriptions using string_agg function. Here is example:

with 
  b(x,d) as (
    values
      (1,'description1'),
      (2,'description2'),
      (4,'description3'),
      (8,'description4')),
  p(y) as (
    values
      (15),
      (12),
      (6))
select
  y,
  string_agg(d,',')
from
  p join b on (x & y != 0)
group by
  y

Upvotes: 1

Related Questions