In DuckDB, the bit_or() function returns the bitwise OR of all bits in a given expression.

Let’s see how the bit_or() function works in DuckDB with some basic examples.

Syntax

The syntax goes like this:

bit_or(arg)

Where arg is the target column or expression that the function operates on.

It’s also possible to provide an over clause to make the function execute as a window function.

Example

Suppose we create the following table and insert data:

CREATE TABLE flag_data (
    ref INTEGER,
    flag INTEGER
);

INSERT INTO flag_data VALUES
    (1, 15),  -- 1111 in binary
    (1, 13),  -- 1101 in binary
    (1, 14),  -- 1110 in binary
    (2, 7),   -- 0111 in binary
    (2, 6);   -- 0110 in binary

Here, the flag column contains integer values. The comments show the 4-bit binary equivalent for the respective value.

Now let’s use bit_or() to return the bitwise OR of all bits in that column:

SELECT 
    bit_or(flag)
FROM flag_data;

Result:

+--------------+
| bit_or(flag) |
+--------------+
| 15           |
+--------------+

We can pass bit_or() to the bin() function in order to get the binary representation:

SELECT 
    bit_or(flag),
    bin(bit_or(flag)) as binary_representation
FROM flag_data;

Result:

+--------------+-----------------------+
| bit_or(flag) | binary_representation |
+--------------+-----------------------+
| 15           | 1111                  |
+--------------+-----------------------+

Grouped Data

Here it is grouped by the ref column:

SELECT ref, 
       bit_or(flag) as bitwise_or,
       bin(bit_or(flag)) as binary_representation
FROM flag_data
GROUP BY ref;

Result:

+-----+------------+-----------------------+
| ref | bitwise_or | binary_representation |
+-----+------------+-----------------------+
| 1   | 15         | 1111                  |
| 2   | 7          | 111                   |
+-----+------------+-----------------------+

Window Function

We can provide an OVER clause in order to make bit_or() operate as a window function.

For this example, let’s create another table:

-- Create a table to store user permissions by department
CREATE TABLE employee_permissions (
    employee_id INTEGER,
    department_id INTEGER,
    hire_date DATE,
    -- Using integer to store permission flags where:
    -- 1 (001): Read
    -- 2 (010): Write
    -- 4 (100): Admin
    permission_flags INTEGER
);

-- Populate with sample data
INSERT INTO employee_permissions VALUES
    (1, 1, '2023-01-01', 1),  -- Read only
    (2, 1, '2023-02-15', 3),  -- Read + Write
    (3, 1, '2023-03-10', 7),  -- Read + Write + Admin
    (4, 2, '2023-01-05', 1),  -- Read only
    (5, 2, '2023-04-20', 2),  -- Write only
    (6, 2, '2023-06-01', 4),  -- Admin only
    (7, 3, '2023-07-01', 3),  -- Read + Write
    (8, 3, '2023-08-15', 5);  -- Read + Admin

We can use the following query to get the cumulative permissions within each department over time:

SELECT 
    employee_id,
    department_id,
    hire_date,
    permission_flags,
    bit_or(permission_flags) OVER (
        PARTITION BY department_id 
        ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_dept_permissions
FROM employee_permissions
ORDER BY department_id, hire_date;

Result:

+-------------+---------------+------------+------------------+-----------------------------+
| employee_id | department_id | hire_date  | permission_flags | cumulative_dept_permissions |
+-------------+---------------+------------+------------------+-----------------------------+
| 1           | 1             | 2023-01-01 | 1                | 1                           |
| 2           | 1             | 2023-02-15 | 3                | 3                           |
| 3           | 1             | 2023-03-10 | 7                | 7                           |
| 4           | 2             | 2023-01-05 | 1                | 1                           |
| 5           | 2             | 2023-04-20 | 2                | 3                           |
| 6           | 2             | 2023-06-01 | 4                | 7                           |
| 7           | 3             | 2023-07-01 | 3                | 3                           |
| 8           | 3             | 2023-08-15 | 5                | 7                           |
+-------------+---------------+------------+------------------+-----------------------------+