PostgreSQL bit_and() Function

The PostgreSQL bit_and() function is an aggregate function that performs a “bitwise AND” operation on all non-null input values.

The bitwise AND processes two binary numbers of the same length, if both corresponding binary bits are 1, the result value of the bit is 1, otherwise it is 0.

bit_and() Syntax

Here is the syntax of the PostgreSQL bit_and() function:

bit_and(expr)

Typically, we use the bit_and() function like:

SELECT bit_and(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression. It accepts a value of type integer or bit.

Return value

The PostgreSQL bit_and() function returns a value ​​of the same type as the input parameters, and it returns the result of performing a “bitwise AND” operation on all non-null input values.

Note that the bit_and() function only handles non-null values. That is, null values ​​are ignored by the bit_and() function.

bit_and() Examples

To demonstrate usages of the PostgreSQL bit_and() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 4 x
UNION
SELECT 5 x
UNION
SELECT 6 x;

 x
---
 4
 6
 5
(3 rows)

The following statement performs the bit_and() operation to the values of the x column:

SELECT bit_and(x)
FROM (
    SELECT 4 x
    UNION
    SELECT 5 x
    UNION
    SELECT 6 x
  ) t;

 bit_and
---------
       4
(1 rows)

Here, the function bit_and() performs a “bitwise AND” operation on the values ​​(4, 5, 6) in the x column, and the calculation steps are as follows:

       4 -> 100
       5 -> 101
       6 -> 110
bit_and() = 100 = 4

So the bit_and() function returns 4.