Bitwise Functions¶

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(x, y) -> bigint()¶

Returns the bitwise AND of x and y in 2’s complement representation.

bitwise_not(x) -> bigint()¶

Returns the bitwise NOT of x in 2’s complement representation.

bitwise_or(x, y) -> bigint()¶

Returns the bitwise OR of x and y in 2’s complement representation.

bitwise_xor(x, y) -> bigint()¶

Returns the bitwise XOR of x and y in 2’s complement representation.

bitwise_shift_left(x, shift, bits) -> bigint()¶

Left shift operation on x (treated as bits-bit integer) shifted by shift:

SELECT bitwise_shift_left(7, 2, 4); -- 12
SELECT bitwise_shift_left(7, 2, 64); -- 28

bitwise_logical_shift_right(x, shift, bits) -> bigint()¶

Logical right shift operation on x (treated as bits-bit integer) shifted by shift:

SELECT bitwise_logical_shift_right(7, 2, 4); -- 1
SELECT bitwise_logical_shift_right(-8, 2, 5); -- 6

bitwise_arithmetic_shift_right(x, shift) -> bigint()¶

Arithmetic right shift operation on x shifted by shift in 2’s complement representation:

SELECT bitwise_arithmetic_shift_right(-8, 2); -- -2
SELECT bitwise_arithmetic_shift_right(7, 2); -- 1

Generic Shift Functions¶

These three functions accept values of integral value types TINYINT, SMALLINT, INTEGER and BIGINT, and shift them by the amount given by shift, returning a value of the same integral type. For all three functions, the amount to shift is given by the bottom bits of the shift parameter, and higher bits of the shift parameter are ignored.

bitwise_left_shift(value, shift) -> [same as value]()¶

Returns the left shifted value of value:

SELECT bitwise_left_shift(TINYINT '7', 2); -- 28
SELECT bitwise_left_shift(TINYINT '-7', 2); -- -28

bitwise_right_shift(value, shift) -> [same as value]()¶

Returns the logical right shifted value of value:

SELECT bitwise_right_shift(TINYINT '7', 2); -- 1
SELECT bitwise_right_shift(SMALLINT -8, 2); -- 16382

bitwise_right_shift_arithmetic(value, shift) -> [same as value]()¶

Returns the arithmetic right shifted value of value:

SELECT bitwise_right_shift_arithmetic(BIGINT '-8', 2); -- -2
SELECT bitwise_right_shift_arithmetic(SMALLINT '7', 2); -- 1

See also bitwise_and_agg() and bitwise_or_agg() in Bitwise Aggregate Functions.