Skip to main content

​
Bitwise functions

​
bit_count

bit_count(x, bits) -> bigint
Count the number of bits set in x (treated as bits-bit signed integer) in 2’s complement representation: 
SELECT bit_count(9, 64); -- 2
SELECT bit_count(9, 8); -- 2
SELECT bit_count(-7, 64); -- 62
SELECT bit_count(-7, 8); -- 6

​
bitwise_and

bitwise_and(x, y) -> bigint
Returns the bitwise AND of x and y in 2’s complement representation. Bitwise AND of 19 (binary: 10011) and 25 (binary: 11001) results in 17 (binary: 10001): 
SELECT bitwise_and(19,25); -- 17

​
bitwise_not

bitwise_not(x) -> bigint
Returns the bitwise NOT of x in 2’s complement representation (NOT x = -x - 1): 
SELECT bitwise_not(-12); --  11
SELECT bitwise_not(19);  -- -20
SELECT bitwise_not(25);  -- -26

​
bitwise_or

bitwise_or(x, y) -> bigint
Returns the bitwise OR of x and y in 2’s complement representation. Bitwise OR of 19 (binary: 10011) and 25 (binary: 11001) results in 27 (binary: 11011): 
SELECT bitwise_or(19,25); -- 27

​
bitwise_xor

bitwise_xor(x, y) -> bigint
Returns the bitwise XOR of x and y in 2’s complement representation. Bitwise XOR of 19 (binary: 10011) and 25 (binary: 11001) results in 10 (binary: 01010): 
SELECT bitwise_xor(19,25); -- 10

​
bitwise_left_shift

bitwise_left_shift(value, shift) -> [same as value]
Returns the left shifted value of value. Shifting 1 (binary: 001) by two bits results in 4 (binary: 00100): 
SELECT bitwise_left_shift(1, 2); -- 4
Shifting 5 (binary: 0101) by two bits results in 20 (binary: 010100): 
SELECT bitwise_left_shift(5, 2); -- 20
Shifting a value by 0 always results in the original value: 
SELECT bitwise_left_shift(20, 0); -- 20
SELECT bitwise_left_shift(42, 0); -- 42
Shifting 0 by a shift always results in 0: 
SELECT bitwise_left_shift(0, 1); -- 0
SELECT bitwise_left_shift(0, 2); -- 0

​
bitwise_right_shift

bitwise_right_shift(value, shift) -> [same as value]
Returns the logical right shifted value of value. Shifting 8 (binary: 1000) by three bits results in 1 (binary: 001): 
SELECT bitwise_right_shift(8, 3); -- 1
Shifting 9 (binary: 1001) by one bit results in 4 (binary: 100): 
SELECT bitwise_right_shift(9, 1); -- 4
Shifting a value by 0 always results in the original value: 
SELECT bitwise_right_shift(20, 0); -- 20
SELECT bitwise_right_shift(42, 0); -- 42
Shifting a value by 64 or more bits results in 0: 
SELECT bitwise_right_shift( 12, 64); -- 0
SELECT bitwise_right_shift(-45, 64); -- 0
Shifting 0 by a shift always results in 0: 
SELECT bitwise_right_shift(0, 1); -- 0
SELECT bitwise_right_shift(0, 2); -- 0

​
bitwise_right_shift_arithmetic

bitwise_right_shift_arithmetic(value, shift) -> [same as value]
Returns the arithmetic right shifted value of value. Returns the same values as bitwise_right_shift when shifting by less than 64 bits. Shifting by 64 or more bits results in 0 for a positive and -1 for a negative value: 
SELECT bitwise_right_shift_arithmetic( 12, 64); --  0
SELECT bitwise_right_shift_arithmetic(-45, 64); -- -1
See also bitwise_and_agg and bitwise_or_agg.