Generic bit string comparison against zero in Postgres

Question

Is there a way to do a non-zero bit string test without hard-coding the bit string width of 0? For example, suppose I have two tables, Users and Features, each with masks, I want to test this: ``` SELECT u.name FROM Users u, Features f WHERE u.mask & f.mask; ``` matching implicit non-zero results. However, SQL requires an explicit boolean result for `WHERE` as opposed to an implicit cast, such as this: ``` SELECT u.name FROM Users u, Features f WHERE (u.mask & f.mask) != 0::BIT(2048); ``` I don't want to hardcode `2048` (or whatever) in this query for a number of reasons. Testing `expr = 0` or `expr > 0` results in a type error. Oddly, I can test `expr = 0::BIT(1)`, but that gives the wrong answer because Postgres does not consider all all-zero bit strings to be equal. ``` select 0::BIT(2) > 0::BIT(1); ?column? ---------- t (1 row) ``` I can create a calculated zero by doing this: ``` SELECT u.name FROM Users u, Features f WHERE (u.mask & f.mask) != (u.mask & ~u.mask); ``` Which works…

Accepted Answer

Short bitstring

To exclude cases where the bitwise AND (`&`) returns a bitstring consisting of nothing but zeros, but the length might change (`B'000...'`), you can use a cast to `integer` (up to `bit(32)`) or `bigint` (up to `bit(64)`):

```
SELECT u.name
FROM   users u
JOIN   features f ON (u.mask & f.mask)::int <> 0;
```

When cast to integer, all of them result in `0`. This also excludes cases where either of the columns is `NULL`. In other words, the result has to include at least one `1`.

Long bitstring

If your values can be longer than 64 bit, you could cast to `text` and check with a regular expression:

```
ON (u.mask & f.mask)::text !~ '^0+$'
```

Pattern explained:

`^` .. beginning of string `0+` .. one or more '0' `$` .. end of string

Or, as the manual informs:

The following SQL-standard functions work on bit strings as well as character strings: `length`, `bit_length`, `octet_length`, `position`, `substring`, `overlay`.

Ergo:

```
ON position('1' IN (u.mask & f.mask)) > 0
```

Generic bit string comparison against zero in Postgres

Solution

Problem