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 but feels like an awful hack.

Any suggestions or insights?

RESULTS

I benchmarked several options provided below. Thanks for the suggestions, Erwin!

Based on a very large data set and 100,000 queries, I found the following constructs resulted in the associated queries per second. Hopefully someone from the Postgres team sees this and provides a generic 0 to speed things up! Unfortunately most generic approaches seem to incur a string conversion which is quite expensive.

Constructs                              |  Queries / s
----------------------------------------+--------------
(u.mask & f.mask) <> 0::BIT(2048)       |  158
(u.mask & f.mask) <> (u.mask # u.mask)  |  135
(u.mask & f.mask) <> (u.mask & ~u.mask) |  125
position('1' IN (u.mask & f.mask)) > 0  |   37
(u.mask & f.mask)::TEXT !~ '^0+$'       |   27

What is a non-zero bit string test? And what exactly do you mean by matching implicit non-zero results Can you explain or add some examples what you want to pass and what to fail? Are your columns defined NOT NULL? Please add your table definition (\d users in psql). What is your Postgres version? — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 28, 2013 at 2:27
I'm looking for a non-zero result. E.g., (B'101' & B'100') != 0::BIT(3), while (B'101' & B'010') = 0::BIT(3). Since I'm using fields (u.mask & f.mask) I don't want to hard-code the BIT length in the query so that I can easily expand the BIT string if needed in the schema without changing many queries in the application. — Joseph
– Joseph, Commented Nov 28, 2013 at 4:14

Erwin Brandstetter · Accepted Answer · 2013-11-28 04:49:37Z

6

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

edited Nov 28, 2013 at 4:49

answered Nov 28, 2013 at 3:10

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joseph Over a year ago

I had tried that too, but it only works for tiny bitmaps. In this case: SELECT 0::BIT(2048)::INT; results in: ERROR: integer out of range.

Erwin Brandstetter Over a year ago

@1.0: Right. I added alternatives for longer bitstrings.

Collectives™ on Stack Overflow

Generic bit string comparison against zero in Postgres

1 Answer 1

Short bitstring

Long bitstring

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Short bitstring

Long bitstring

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related