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

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
``````