bit_and(), bit_or(), bool_and(), bool_or()
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