bit varying in Postgres to be queried by sub-string pattern

Question

The following Postgres table contains some sample content where the binary data is stored as bit varying (https://www.postgresql.org/docs/10/datatype-bit.html):

ID   |   Binary data
----------------------
1    |    01110        
2    |    0111       
3    |    011         
4    |    01        
5    |    0         
6    |    00011      
7    |    0001        
8    |    000    
9    |    00          
10   |    0  
11   |    110
12   |    11
13   |    1

Q: Is there any query (either native SQL or as Postgres function) to return all rows where the binary data field is equal to all sub-strings of the target bit array. To make it more clear lets look at the example search value 01101:

01101 -> no result
0110 -> no result
011 -> 3
01 -> 4
0 -> 5, 10

The result returned should contain the rows: 3, 4, 5 and 10.

Edit: The working query is (thanks to Laurenz Albe):

SELECT * FROM table WHERE '01101' LIKE (table.binary_data::text || '%')

Furthermore I found this discussion about Postgres bit with fixed size vs bit varying helpful: PostgreSQL Bitwise operators with bit varying "cannot AND bit strings of different sizes"

Please set up a db/sql fiddle with sample data so the types are clear. — Gordon Linoff
– Gordon Linoff, Commented Oct 23, 2020 at 13:21

Laurenz Albe · Accepted Answer · 2020-10-23 14:29:07Z

1

How about

WHERE '01101' LIKE (col2::text || '%')

answered Oct 23, 2020 at 14:29

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-10-23 12:52:02Z

0

I think you are looking for bitwise and:

where col2 & B'01101' = col2

answered Oct 23, 2020 at 12:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

developer346 Over a year ago

Thanks. I tried with 'SELECT * from test where test.binary_data & B'01101' = test.binary_data' result: Query 1 ERROR: ERROR: cannot AND bit strings of different sizes

Collectives™ on Stack Overflow

bit varying in Postgres to be queried by sub-string pattern

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related