9.6. Bit String Functions and Operators

This section describes functions and operators for examining and manipulating bit strings, that is values of the types bit and bit varying . Aside from the usual comparison operators, the operators shown in Table 9-10 can be used. Bit string operands of & , | , and # must be of equal length. When bit shifting, the original length of the string is preserved, as shown in the examples.

Table 9-10. Bit String Operators

Operator Description Example Result
|| concatenation B'10001' || B'011' 10001011
& bitwise AND B'10001' & B'01101' 00001
| bitwise OR B'10001' | B'01101' 11101
# bitwise XOR B'10001' # B'01101' 11100
~ bitwise NOT ~ B'10001' 01110
<< bitwise shift left B'10001' << 3 01000
>> bitwise shift right B'10001' >> 2 00100

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

In addition, it is possible to cast integral values to and from type bit . Some examples:

44::bit(10) 0000101100 44::bit(3) 100 cast(-44 as bit(12)) 111111010100 '1110'::bit(4)::integer 14

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

Note: Prior to PostgreSQL 8.0, casting an integer to bit(n) would copy the leftmost n bits of the integer, whereas now it copies the rightmost n bits. Also, casting an integer to a bit string width wider than the integer itself will sign-extend on the left.