bit_and(), bit_or(), bool_and(), bool_or()

This page documents a preview version. v2.23 Preview
Preview includes features under active development and is for development and testing only.
For production, use the latest stable version (v2024.1).

These four functions bear a strong mutual family resemblance and so they are all described in the same section.

The invocation semantics and syntax, using the GROUP BY syntax or the OVER syntax, are the same as were described for avg(), count, max(), min(), and sum().

The bit data type represents an array of false/true values of arbitrary length. For example, bit(13) is legal and, when not atomically null, it inevitably represents thirteen false/true values. The OR and AND rules for each bit are described by the well-known two-by-two matrix, with the axes labeled "0" and "1" (conventionally taken to represent true and false). In contrast, the OR and AND rules for boolean values are ordinarily (and famously) described by a three-by-three matrix, with the axes labelled null, true, and false. However, the general rule for aggregate functions that any null elements in the to-be-aggregated set are ignored trumps the normal three-state logic rules. This means that the semantics of the bit_and() and bit_or() aggregate functions are described in the same way as are the semantics of the bool_and() and bool_or() aggregate functions. The difference is only that the rules for bool_and() and bool_or() are specified just for the set of scalar false/true values while the rules for bit_and() and bit_or() are specified for each bit in the bit vector (*and in the same way for each bit).

bit_and()

Signature:

input value:       bit, smallint, int, bigint

return value:      < Same as the input value's data type. >

Purpose: Returns a value that represents the outcome of the applying the two-by-two matrix AND rule to each aligned set of bits for the set of NOT NULL input values.

bit_or()

Signature:

input value:       bit, smallint, int, bigint

return value:      < Same as the input value's data type. >

Purpose: Returns a value that represents the outcome of the applying the two-by-two matrix OR rule to each aligned set of bits for the set of NOT NULL input values.

bool_and()

Signature:

input value:       boolean

return value:      boolean

Purpose: Returns a value that represents the outcome of the applying the two-by-two matrix AND rule to the set of NOT NULL input boolean values.

bool_or()

Signature:

input value:       anyelement, int, [, anyelement]

return value:      anyelement

Purpose: Returns a value that represents the outcome of the applying the two-by-two matrix OR rule to the set of NOT NULL input boolean values.

Examples

First, create the test table.

drop table if exists t cascade;
create table t(
  k     int    primary key,
  class int    not null,
  b1    bit(8),
  b2    boolean);

insert into t(k, class, b1, b2) values
  (1, 1, '00000001', true),
  (2, 1, '00000010', true),
  (3, 1, '00000100', null),
  (4, 1, '00001000', false),
  (5, 2, '10011000', true),
  (6, 2, '10100100', true),
  (7, 2, null,       true),
  (8, 2, '10000001', true);

\pset null <null>
select
  k,
  class,
  b1::text,
  b2::text
from t
order by k, class;

This is the result:

 k | class |    b1    |   b2
---+-------+----------+--------
 1 |     1 | 00000001 | true
 2 |     1 | 00000010 | true
 3 |     1 | 00000100 | <null>
 4 |     1 | 00001000 | false
 5 |     2 | 10011000 | true
 6 |     2 | 10100100 | true
 7 |     2 | <null>   | true

GROUP BY syntax example

Try this:

select
  class,
  bit_and(b1)::text  as "bit_and(b1)",
  bit_or(b1)::text   as "bit_or(b1)",
  bool_and(b2)::text as "bool_and(b2)",
  bool_or(b2)::text  as "bool_or(b2)"
from t
group by class
order by class;

This is the result:

 class | bit_and(b1) | bit_or(b1) | bool_and(b2) | bool_or(b2)
-------+-------------+------------+--------------+-------------
     1 | 00000000    | 00001111   | false        | true
     2 | 10000000    | 10111101   | true         | true

OVER syntax

Try this:

select
  k,
  class,
  (bit_and(b1)  over w)::text as "bit_and(b1)",
  (bit_or(b1)   over w)::text as "bit_or(b1)",
  (bool_and(b2) over w)::text as "bool_and(b2)",
  (bool_or(b2)  over w)::text as "bool_or(b2)"
from t
window w as (
  partition by class
  order by k
  range between unbounded preceding and current row)
order by k, class;

This is the result:

 k | class | bit_and(b1) | bit_or(b1) | bool_and(b2) | bool_or(b2)
---+-------+-------------+------------+--------------+-------------
 1 |     1 | 00000001    | 00000001   | true         | true
 2 |     1 | 00000000    | 00000011   | true         | true
 3 |     1 | 00000000    | 00000111   | true         | true
 4 |     1 | 00000000    | 00001111   | false        | true
 5 |     2 | 10011000    | 10011000   | true         | true
 6 |     2 | 10000000    | 10111100   | true         | true
 7 |     2 | 10000000    | 10111100   | true         | true
 8 |     2 | 10000000    | 10111101   | true         | true