Postgres Boolean data type

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 as t, true, y, yes, on, 1.
• FALSE can also be represented as f, 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:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    is_active BOOLEAN,
    has_paid_subscription BOOLEAN
);

INSERT INTO users (username, is_active, has_paid_subscription)
VALUES 
    ('alice', TRUE, TRUE),
    ('bob', TRUE, FALSE),
    ('charlie', FALSE, TRUE),
    ('david', NULL, NULL),
    ('eve', FALSE, FALSE);

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.

SELECT *
FROM users
WHERE is_active = TRUE;

This query returns the following:

| id | username | is_active | has_paid_subscription |
|----|----------|-----------|-----------------------|
| 1  | alice    | t         | t                     |
| 2  | bob      | t         | f                     |

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.

SELECT username,
    CASE
        WHEN is_active = TRUE AND has_paid_subscription = TRUE THEN 'Active Paid'
        WHEN is_active = TRUE AND has_paid_subscription = FALSE THEN 'Active Free'
        WHEN is_active = FALSE AND has_paid_subscription = TRUE THEN 'Inactive Paid'
        WHEN is_active = FALSE AND has_paid_subscription = FALSE THEN 'Inactive Free'
        ELSE 'Unknown'
    END AS user_status
FROM users;

This query returns the following:

| username | user_status   |
|----------|---------------|
| alice    | Active Paid   |
| bob      | Active Free   |
| charlie  | Inactive Paid |
| david    | Unknown       |
| eve      | Inactive Free |

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.

SELECT id, username
FROM users
WHERE is_active = TRUE AND has_paid_subscription = FALSE;

This query returns the following:

| id | username |
|----|----------|
| 2  | bob      |

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.

SELECT bool_or(has_paid_subscription) AS inactive_paid_users
FROM users
WHERE is_active = FALSE;

This query returns the following:

| inactive_paid_users |
|---------------------|
| t                   |

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.

WITH contacts (user_id, email) AS (
    VALUES
    (1, 'alice@email.com'),
    (2, 'bob@email.com'),
    (3, 'charlie@email.com'),
    (4, 'david@email.com'),
    (5, 'eve@email.com')
)
SELECT u.id, u.username, c.email
FROM users u
JOIN contacts c ON u.id = c.user_id AND u.is_active = TRUE;

This query returns the following:

| id | username | email           |
|----|----------|-----------------|
| 1  | alice    | alice@email.com |
| 2  | bob      | bob@email.com   |

Additional considerations

• NULL: NULL in boolean terms indicates an unknown state, which is neither TRUE nor FALSE. In conditional statements, NULL values will not equate to FALSE.
• Type Casting: Be mindful when converting Booleans to other data types. For instance, casting a Boolean to an integer results in 1 for TRUE and 0 for FALSE. 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

• PostgreSQL Boolean Type documentation