BIT_COUNT

Return the number of bits set to 1 in an integer (population count).

Category: miscReturns: INTDialect: PostgreSql

Syntax

BIT_COUNT(n)

Description

## Overview Returns the number of bits set to 1 in the binary representation of the input integer. This quantity is also known as the population count or Hamming weight. Use this function to count active flags in bitmask columns, measure the size of a bitmap, or compute Hamming distances when combined with BIT_XOR. The count is taken over all bits of the integer, including the sign bit. Negative values have many set bits because the sign bit and the high-order bits are 1 under two's complement. ## Behavior - Returns NULL if the argument is NULL. - Returns an INT count in the range 0 to the integer width (for example, 0 to 32 for INT, 0 to 64 for BIGINT). - Counts the set bits across the full promoted width of the argument. - Result is deterministic and side effect free. ## Bit semantics - `BIT_COUNT(BIT_XOR(a, b))` is the Hamming distance between a and b. - `BIT_COUNT(-1)` equals the integer width (all ones). - `BIT_COUNT(0)` is 0. - The count grows linearly with the number of set bits; it is O(width) internally but has a single fused machine instruction on most platforms. ## Compatibility - Matches the PG-compat BIT_COUNT function and the equivalent population-count primitive used in other analytical SQL dialects.

Parameters

Name	Type	Description
`n`		Specifies the integer value whose set bits are counted. The full promoted width of the argument is examined, including the sign bit.

Examples

-- Three bits set in 7 (binary 111)
SELECT BIT_COUNT(7);  -- 3

-- Low byte fully set
SELECT BIT_COUNT(255);  -- 8

-- Zero has zero set bits
SELECT BIT_COUNT(0);  -- 0

-- Count active feature flags per account
SELECT account_id, BIT_COUNT(feature_flags) AS active_features
FROM security.catalog.accounts;

-- Hamming distance between two masks
SELECT user_id, BIT_COUNT(BIT_XOR(current_mask, previous_mask)) AS bits_changed
FROM security.catalog.user_masks;

-- Population count of a negative value (all sign bits count)
SELECT BIT_COUNT(-1);  -- 32 for INT, 64 for BIGINT

Pitfalls

BIT_COUNT on a negative number counts the sign bit and the high-order 1s. For example, `BIT_COUNT(-1)` returns 32 (INT) or 64 (BIGINT), not 1. If you want a bit count on the absolute value, apply ABS first.
The result depends on the promoted integer width. Casting a value to a wider type can change the count (though only by adding zero bits; one-bits are preserved).
Do not call BIT_COUNT on a string column; cast the text to INT first. Passing a string raises a type error, not a silent zero.
This is the scalar (per-row) population count, not the SQL aggregate bit_count. Wrap in SUM or use an explicit aggregate to total bit counts across rows.