Postgres Boolean data type
Represent truth values in Postgres
In Postgres, the Boolean datatype is designed to store truth values. A Boolean column can hold one of three states: true
, false
, or NULL
representing unknown or missing values.
For instance, Boolean values can be used in a dataset to represent the status of an order, whether a user is active, or whether a product is in stock. A Boolean value could also be produced as a result of comparisons or logical operations.
Storage and syntax
In SQL statements, Boolean values are represented by the keywords TRUE
, FALSE
, and NULL
. Postgres is flexible and allows for various textual representations of these values:
TRUE
can also be represented ast
,true
,y
,yes
,on
,1
.FALSE
can also be represented asf
,false
,n
,no
,off
,0
.
A boolean value is stored as a single byte.
Example usage
Consider a table of users for a web application. We can add a Boolean column to represent whether a user is active or not.
The query below creates a users
table and inserts some sample data:
Say we want to find all the users currently active on the website. The WHERE
clause accepts a Boolean expression, so we can filter down to the rows where the is_active
column is TRUE
.
This query returns the following:
Other examples
Conditional logic
Boolean data types are commonly used in conditional statements like WHERE
, IF
, and CASE
. For example, the CASE
statement is a control flow structure that allows you to perform IF-THEN-ELSE
logic in SQL.
In the query below, we categorize users based on their activity and account type.
This query returns the following:
Boolean expressions
Boolean expressions combine multiple boolean values using operators like AND
, OR
, and NOT
. These expressions return boolean values and are crucial in complex SQL queries.
For example, we can use a Boolean expression to find all the users who are active but don't have a paid subscription yet.
This query returns the following:
Boolean aggregations
Postgres also supports aggregating over a set of Boolean values, using functions like bool_and()
and bool_or()
.
For example, we can query to check that no inactive users have a paid subscription.
This query returns the following:
This indicates there is at least one inactive user with an ongoing subscription. We should probably email them a reminder to log in.
Boolean in join conditions
Booleans can be effectively used in the JOIN
clause to match rows across tables.
In the query below, we join the users
table with the table containing contact information to send a promotional email to all active users.
This query returns the following:
Additional considerations
- NULL:
NULL
in boolean terms indicates an unknown state, which is neitherTRUE
norFALSE
. In conditional statements,NULL
values will not equate toFALSE
. - Type Casting: Be mindful when converting Booleans to other data types. For instance, casting a Boolean to an integer results in
1
forTRUE
and0
forFALSE
. This behavior is useful in aggregations or mathematical operations. - Indexing: Using Booleans in indexing might not always be efficient, especially if the distribution of true and false values is uneven.
Resources
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.