Bit-Wise Function Examples
The USERS table in the TICKIT sample database contains several Boolean columns that indicate whether each user is known to like different types of events, such as sports, theatre, opera, and so on. For example:
select userid, username, lastname, city, state, likesports, liketheatre from users limit 10; userid | username | lastname | city | state | likesports | liketheatre --------+----------+-----------+--------------+-------+------------+------------- 1 | JSG99FHE | Taylor | Kent | WA | t | t 9 | MSD36KVR | Watkins | Port Orford | MD | t | f
Assume that a new version of the USERS table is built in a different way, with a single integer column that defines (in binary form) eight types of events that each user likes or dislikes. In this design, each bit position represents a type of event, and a user who likes all eight types has all eight bits set to 1 (as in the first row of the following table). A user who does not like any of these events has all eight bits set to 0 (see second row). A user who likes only sports and jazz is represented in the third row:
In the database table, these binary values could be stored in a single LIKES column as integers:
|User||Binary value||Stored value (integer)|
BIT_AND and BIT_OR Examples
Given that meaningful business information is stored in integer columns, you can use bit-wise functions to extract and aggregate that information. The following query applies the BIT_AND function to the LIKES column in a table called USERLIKES and groups the results by the CITY column.
These results can be interpreted as follows:Copy
select city, bit_and(likes) from userlikes group by city order by city; city | bit_and ---------------+--------- Los Angeles | 0 Sacramento | 0 San Francisco | 0 San Jose | 64 Santa Barbara | 192 (5 rows)
The integer value
192for Santa Barbara translates to the binary value
11000000. In other words, all users in this city like sports and theatre, but not all users like any other type of event.
01000000, so for users in San Jose, the only type of event that they all like is theatre.
The values of
0for the other three cities indicate that no "likes" are shared by all users in those cities.
If you apply the BIT_OR function to the same data, the results are as follows:
For four of the cities listed, all of the event types are liked by at least one user (Copy
select city, bit_or(likes) from userlikes group by city order by city; city | bit_or ---------------+-------- Los Angeles | 127 Sacramento | 255 San Francisco | 255 San Jose | 255 Santa Barbara | 255 (5 rows)
255=11111111). For Los Angeles, all of the event types except sports are liked by at least one user (
BOOL_AND and BOOL_OR Examples
You can use the Boolean functions against either Boolean expressions or integer expressions. For example, the following query return results from the standard USERS table in the TICKIT database, which has several Boolean columns.
The BOOL_OR function returns
true for all five rows. At least one
user in each of those states likes sports. The BOOL_AND function returns
false for all five rows. Not all users in each of those states likes
select state, bool_or(likesports), bool_and(likesports) from users group by state order by state limit 5; state | bool_or | bool_and -------+-------------------- AB | t | f AK | t | f AL | t | f AZ | t | f BC | t | f (5 rows)