Bit String Functions and Operators

Aside from the usual comparison operators, the following operators can be used. Bit string operands of &, |, and # must be of equal length. When bit shifting, the original length of the string is preserved by zero padding (if necessary).

1
2
3
4
5

SELECT B'10001' || B'011' AS RESULT;
  result
----------
 10001011
(1 row)

1
2
3
4
5

SELECT B'10001' & B'01101' AS RESULT;
 result 
--------
 00001
(1 row)

1
2
3
4
5

SELECT B'10001' | B'01101' AS RESULT;
 result 
--------
 11101
(1 row)

1
2
3
4
5

SELECT B'10001' # B'01101' AS RESULT;
 result 
--------
 11100
(1 row)

1
2
3
4
5

SELECT ~B'10001'AS RESULT;
 result  
----------
 01110
(1 row)

1
2
3
4
5

SELECT B'10001' << 3 AS RESULT;
 result  
----------
 01000
(1 row)

1
2
3
4
5

SELECT B'10001' >> 2 AS RESULT;
 result  
----------
 00100
(1 row)

The following SQL-standard functions work on bit strings as well as character strings: length, bit_length, octet_length, position, substring, and overlay.

The following functions work on bit strings as well as binary strings: get_bit and set_bit. When working with a bit string, these functions number the first (leftmost) bit of the string as bit 0.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SELECT 44::bit(10) AS RESULT;
   result
------------
 0000101100
(1 row)

SELECT 44::bit(3) AS RESULT;
 result 
--------
 100
(1 row)

SELECT cast(-44 as bit(12)) AS RESULT;
    result    
--------------
 111111010100
(1 row)

SELECT '1110'::bit(4)::integer AS RESULT;
 result 
--------
     14
(1 row)

Casting to just "bit" means casting to bit(1), and so will deliver only the least significant bit of the integer.