Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Bit-wise aggregate functions Bit-wise aggregate functions compute bit operations to perform aggregation of integer columns and columns that can be converted or rounded to integer values. **Topics** + [Using NULLs in bit-wise aggregations](#c_bitwise_aggregate_functions-nulls-in-bit-wise-aggregations) + [DISTINCT support for bit-wise aggregations](#distinct-support-for-bit-wise-aggregations) + [Overview examples for bit-wise functions](#r_bitwise_example) + [BIT\$1AND function](r_BIT_AND.md) + [BIT\$1OR function](r_BIT_OR.md) + [BOOL\$1AND function](r_BOOL_AND.md) + [BOOL\$1OR function](r_BOOL_OR.md) ## Using NULLs in bit-wise aggregations When you apply a bit-wise function to a column that is nullable, any NULL values are eliminated before the function result is calculated. If no rows qualify for aggregation, the bit-wise function returns NULL. The same behavior applies to regular aggregate functions. Following is an example. ``` select sum(venueseats), bit_and(venueseats) from venue where venueseats is null; sum | bit_and ------+--------- null | null (1 row) ``` ## DISTINCT support for bit-wise aggregations As other aggregate functions do, bit-wise functions support the DISTINCT keyword. However, using DISTINCT with these functions has no impact on the results. The first instance of a value is sufficient to satisfy bit-wise AND or OR operations. It makes no difference if duplicate values are present in the expression being evaluated. Because the DISTINCT processing is likely to incur some query execution overhead, we recommend that you don't use DISTINCT with bit-wise functions. ## Overview examples for bit-wise functions Following, you can find some overview examples demonstrating how to work with the bit-wise functions. You can also find specific code examples with each function description. Examples for the bit-wise functions are based on the TICKIT sample database. 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. An example follows. ``` 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. In this new version, 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. 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 doesn't like any of these events has all eight bits set to 0 (see the second row). A user who likes only sports and jazz is represented in the third row following. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_bitwise_aggregate_functions.html) In the database table, these binary values can be stored in a single LIKES column as integers, as shown following. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_bitwise_aggregate_functions.html) # BIT\$1AND function The BIT\$1AND function runs bit-wise AND operations on all of the values in a single integer column or expression. This function aggregates each bit of each binary value that corresponds to each integer value in the expression. The BIT\$1AND function returns a result of `0` if none of the bits is set to 1 across all of the values. If one or more bits is set to 1 across all values, the function returns an integer value. This integer is the number that corresponds to the binary value for the those bits. For example, a table contains four integer values in a column: 3, 7, 10, and 22. These integers are represented in binary form as follows: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_BIT_AND.html) A BIT\$1AND operation on this dataset finds that all bits are set to `1` in the second-to-last position only. The result is a binary value of `00000010`, which represents the integer value `2`. Therefore, the BIT\$1AND function returns `2`. ## Syntax ``` BIT_AND ( [DISTINCT | ALL] expression ) ``` ## Arguments *expression * The target column or expression that the function operates on. This expression must have an INT, INT2, or INT8 data type. The function returns an equivalent INT, INT2, or INT8 data type. DISTINCT \$1 ALL With the argument DISTINCT, the function eliminates all duplicate values for the specified expression before calculating the result. With the argument ALL, the function retains all duplicate values. ALL is the default. For more information, see [DISTINCT support for bit-wise aggregations](c_bitwise_aggregate_functions.md#distinct-support-for-bit-wise-aggregations). ## 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\$1AND function to the LIKES column in a table called USERLIKES and groups the results by the CITY column. ``` 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) ``` You can interpret these results as follows: + The integer value `192` for 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. + The integer `64` translates to `01000000`. So, for users in San Jose, the only type of event that they all like is theatre. + The values of `0` for the other three cities indicate that no "likes" are shared by all users in those cities. # BIT\$1OR function The BIT\$1OR function runs bit-wise OR operations on all of the values in a single integer column or expression. This function aggregates each bit of each binary value that corresponds to each integer value in the expression. For example, suppose that your table contains four integer values in a column: 3, 7, 10, and 22. These integers are represented in binary form as follows. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/r_BIT_OR.html) If you apply the BIT\$1OR function to the set of integer values, the operation looks for any value in which a `1` is found in each position. In this case, a `1` exists in the last five positions for at least one of the values, yielding a binary result of `00011111`; therefore, the function returns `31` (or `16 + 8 + 4 + 2 + 1`). ## Syntax ``` BIT_OR ( [DISTINCT | ALL] expression ) ``` ## Arguments *expression * The target column or expression that the function operates on. This expression must have an INT, INT2, or INT8 data type. The function returns an equivalent INT, INT2, or INT8 data type. DISTINCT \$1 ALL With the argument DISTINCT, the function eliminates all duplicate values for the specified expression before calculating the result. With the argument ALL, the function retains all duplicate values. ALL is the default. For more information, see [DISTINCT support for bit-wise aggregations](c_bitwise_aggregate_functions.md#distinct-support-for-bit-wise-aggregations). ## Example The following query applies the BIT\$1OR function to the LIKES column in a table called USERLIKES and groups the results by the CITY column. ``` 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) ``` For four of the cities listed, all of the event types are liked by at least one user (`255=11111111`). For Los Angeles, all of the event types except sports are liked by at least one user (`127=01111111`). # BOOL\$1AND function The BOOL\$1AND function operates on a single Boolean or integer column or expression. This function applies similar logic to the BIT\$1AND and BIT\$1OR functions. For this function, the return type is a Boolean value (`true` or `false`). If all values in a set are true, the BOOL\$1AND function returns `true` (`t`). If any value is false, the function returns `false` (`f`). ## Syntax ``` BOOL_AND ( [DISTINCT | ALL] expression ) ``` ## Arguments *expression * The target column or expression that the function operates on. This expression must have a BOOLEAN or integer data type. The return type of the function is BOOLEAN. DISTINCT \$1 ALL With the argument DISTINCT, the function eliminates all duplicate values for the specified expression before calculating the result. With the argument ALL, the function retains all duplicate values. ALL is the default. For more information, see [DISTINCT support for bit-wise aggregations](c_bitwise_aggregate_functions.md#distinct-support-for-bit-wise-aggregations). ## 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\$1AND function returns `false` for all five rows. Not all users in each of those states likes sports. ``` select state, bool_and(likesports) from users group by state order by state limit 5; state | bool_and ------+--------- AB | f AK | f AL | f AZ | f BC | f (5 rows) ``` # BOOL\$1OR function The BOOL\$1OR function operates on a single Boolean or integer column or expression. This function applies similar logic to the BIT\$1AND and BIT\$1OR functions. For this function, the return type is a Boolean value (`true`, `false`, or `NULL`). If one or more values in a set is `true`, the BOOL\$1OR function returns `true` (`t`). If all values in a set are `false`, the function returns `false` (`f`). NULL can be returned if the value is unknown. ## Syntax ``` BOOL_OR ( [DISTINCT | ALL] expression ) ``` ## Arguments *expression * The target column or expression that the function operates on. This expression must have a BOOLEAN or integer data type. The return type of the function is BOOLEAN. DISTINCT \$1 ALL With the argument DISTINCT, the function eliminates all duplicate values for the specified expression before calculating the result. With the argument ALL, the function retains all duplicate values. ALL is the default. See [DISTINCT support for bit-wise aggregations](c_bitwise_aggregate_functions.md#distinct-support-for-bit-wise-aggregations). ## Examples You can use the Boolean functions with 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\$1OR function returns `true` for all five rows. At least one user in each of those states likes sports. ``` select state, bool_or(likesports) from users group by state order by state limit 5; state | bool_or ------+-------- AB | t AK | t AL | t AZ | t BC | t (5 rows) ``` The following example returns NULL. ``` SELECT BOOL_OR(NULL = '123') bool_or ------ NULL ```