Bitwise Operations with PostgreSQL

Bitwise operations allow for direct manipulation of the bits that make up one or more numbers. These operations are extremely useful in scenarios where we need high performance and granular control over data, such as in binary processing or permission control systems. Some of the most common operators used in bitwise operations are AND, OR, and XOR.

How Do Bitwise Operations Work?

The process of applying a bitwise operator to integer numbers can be described in three simple steps:

1. Conversion to Binary: The numbers are converted to their binary representation.
2. Application of the Operator: The chosen operator is applied bit by bit, comparing the bits of each number.
3. Conversion to Integer: The final result, still in binary, is converted back to decimal format.

Practical Example

To illustrate how these operations work, we will use the following collection of five numbers: [1, 2, 3, 4, 5]. The binary representation of these numbers is: [001, 010, 011, 100, 101].

AND Operator

The AND operator compares the bits of two numbers and returns 1 only if both bits are 1. Otherwise, the result will be 0.

Example 1: Bitwise AND between 1 and 2

Let’s calculate the bitwise AND between 1 and 2:

• Binary of 1: 001
• Binary of 2: 010

Applying the operation bit by bit:

  001
& 010
------
  000

The binary result is 000, which corresponds to the decimal value 0. Therefore, the bitwise AND between 1 and 2 is 0.

Example 2: Bitwise AND between 2 and 3

Now, let’s calculate the bitwise AND between 2 and 3:

• Binary of 2: 010
• Binary of 3: 011

Applying the AND operation:

  010
& 011
------
  010

The binary result is 010, which corresponds to the decimal value 2. Therefore, the bitwise AND between 2 and 3 is 2.

Applying Bitwise AND to All Elements in the Collection

If we iteratively apply the AND operator to the entire collection [1, 2, 3, 4, 5], the calculation will be:

  001
& 010
& 011
& 100
& 101
------
  000

The final result is 000 in binary, which means 0 in decimal. This occurs because, when comparing all the numbers bit by bit, there is no column where the value is consistently 1 across all numbers.

OR Operator

The OR operator compares the bits of two numbers, returning 1 if at least one of the bits is 1. Otherwise, the result will be 0.

Applying Bitwise OR to All Elements in the Collection

Let’s apply the OR operator to all the numbers in the collection [1, 2, 3, 4, 5]:

  001
| 010
| 011
| 100
| 101
------
  111

The binary result is 111, which corresponds to the decimal value 7. The final value is 7 because, in all columns, there is at least one bit with the value 1.

XOR Operator

The XOR operator compares the bits of two numbers, returning 1 if the bits are different, and 0 if they are the same.

Applying Bitwise XOR to All Elements in the Collection

Now, let’s apply the XOR operator to all the numbers in the collection [1, 2, 3, 4, 5]:

   001
⊕ 010
⊕ 011
⊕ 100
⊕ 101

The calculation will be performed step by step:

1. 001 ⊕ 010 = 011 (which is 3 in decimal)
2. 011 ⊕ 011 = 000 (which is 0 in decimal)
3. 000 ⊕ 100 = 100 (which is 4 in decimal)
4. 100 ⊕ 101 = 001 (which is 1 in decimal)

Therefore, the final result of the XOR operation for all elements in the collection is 1.

Aggregation functions in SQL operate on sets of data. Thus, we can provide a list with the collection defined in this article and use the functions bit_and, bit_or, and bit_xor to verify the results of the calculated operations.

To do this, execute the following script in PostgreSQL:

WITH IntegerCollection (Value) AS (
    SELECT GENERATE_SERIES(1, 5)
)
SELECT
    bit_and(Value) AS AND,
    bit_or(Value) AS OR,
    bit_xor(Value) AS XOR
FROM IntegerCollection;

Check that the result, as expected, is 0, 7, and 1.