BIT_XOR

Return the bitwise exclusive OR of two integer values.

Category: miscReturns: INTDialect: PostgreSql

Syntax

BIT_XOR(a, b)

Description

## Overview Returns an integer whose bits are the bitwise exclusive OR of the corresponding bits in the two operands. A bit in the result is 1 only if the bits at the same position differ between the two inputs. Use this function to toggle specific bits, compute simple checksums, or measure the Hamming distance between two bitmaps. BIT_XOR is the function form of the `^` operator (or `#` in PG-compat SQL) and is often used in tandem with BIT_COUNT to count differing bits between two values. ## Behavior - Returns NULL if either argument is NULL. - Operates on the two's complement bit pattern of the integers. - Both arguments are promoted to the widest integer type among the two before the operation. - Result is deterministic and side effect free. - BIT_XOR(x, x) equals 0. BIT_XOR(x, 0) equals x. BIT_XOR(x, -1) equals BIT_NOT(x). - Self-inverse: applying XOR with the same mask twice restores the original value. ## Bit semantics - Bits are numbered from 0 at the least significant end. - To toggle bit n, XOR with `1 << n`: `BIT_XOR(x, 1 << n)`. - `BIT_COUNT(BIT_XOR(a, b))` is the Hamming distance between a and b. ## Compatibility - Equivalent to the standard `^` or PG-compat `#` operator and to the PG-compat function of the same name.

Parameters

Name	Type	Description
`a`		Specifies the first integer operand. Treated as a two's complement signed value across the full width of the argument type.
`b`		Specifies the second integer operand. Must be the same integer width as the first operand after implicit promotion.

Examples

-- Basic XOR of 12 (1100) and 10 (1010)
SELECT BIT_XOR(12, 10);  -- 6

-- XOR with itself returns zero (useful for parity)
SELECT BIT_XOR(42, 42);  -- 0

-- XOR with zero is identity
SELECT BIT_XOR(99, 0);  -- 99

-- Toggle bit 2 of a flags column for every row
SELECT setting_id, BIT_XOR(flags, 4) AS toggled_flags
FROM security.catalog.settings;

-- Detect bits that differ between two masks
SELECT BIT_COUNT(BIT_XOR(a.mask, b.mask)) AS hamming_distance
FROM security.catalog.user_masks a
JOIN security.catalog.user_masks b ON a.user_id = b.user_id;

-- NULL propagation
SELECT BIT_XOR(NULL, 7);  -- NULL

Pitfalls

BIT_XOR is not a cryptographic primitive. Do not use it as a substitute for real encryption or a keyed MAC; it leaks the key immediately with two known plaintexts.
The self-inverse property is easy to misuse: `BIT_XOR(BIT_XOR(x, k), k)` returns x. This is intentional for toggling, but can hide bugs when the same column is XORed twice by accident.
As with BIT_AND and BIT_OR, this function does not aggregate. If you want to XOR all values in a group, use an explicit aggregation wrapper or fold through a UDAF.
Integer promotion to a wider type applies before XOR, which can silently widen the result width. Cast back to the original type if the downstream column is narrower.