PostgreSQL Boolean Type

Introduction

The Boolean data type is one of the fundamental data types in PostgreSQL that represents the truth values of Boolean logic. It can store only one of three possible states: TRUE, FALSE, or NULL (representing an unknown state). This simple yet powerful data type is essential for expressing logical conditions in your database design and queries.

In this tutorial, we'll explore how to:

• Define Boolean columns in PostgreSQL
• Insert Boolean values with different input formats
• Perform Boolean operations
• Use Boolean values in WHERE clauses
• Apply Boolean types in real-world scenarios

Creating Tables with Boolean Columns

To define a column with a Boolean data type in PostgreSQL, you use the BOOLEAN keyword or its alias BOOL.

sql

CREATE TABLE task_list (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    is_completed BOOLEAN DEFAULT FALSE,
    is_priority BOOLEAN
);

In this example, we've created a simple task list table with two Boolean columns:

• is_completed: A Boolean with a default value of FALSE
• is_priority: A Boolean that can be TRUE, FALSE, or NULL

Inserting Boolean Values

PostgreSQL offers flexible input formats for Boolean values:

TRUE Values	FALSE Values
TRUE	FALSE
't'	'f'
'true'	'false'
'yes'	'no'
'y'	'n'
'1'	'0'

Let's insert some records into our task list:

sql

INSERT INTO task_list (task_name, is_completed, is_priority) 
VALUES 
    ('Complete PostgreSQL tutorial', FALSE, TRUE),
    ('Buy groceries', 'f', 't'),
    ('Pay electricity bill', '0', '1'),
    ('Call dentist', 'no', 'yes'),
    ('Submit project proposal', 'false', NULL);

Let's verify our inserts:

sql

SELECT * FROM task_list;

Result:

 task_id |         task_name         | is_completed | is_priority 
---------+---------------------------+--------------+-------------
       1 | Complete PostgreSQL tutorial | f            | t
       2 | Buy groceries              | f            | t
       3 | Pay electricity bill       | f            | t
       4 | Call dentist               | f            | t
       5 | Submit project proposal    | f            | NULL

Notice that despite using different input formats, PostgreSQL standardizes the display to t for TRUE and f for FALSE when querying the data.

Boolean Operations

PostgreSQL supports standard Boolean operators:

• AND (AND): Returns TRUE if both operands are TRUE
• OR (OR): Returns TRUE if at least one operand is TRUE
• NOT (NOT): Returns the opposite Boolean value

sql

SELECT 
    task_name,
    is_completed,
    is_priority,
    is_completed AND is_priority AS "completed_priority",
    is_completed OR is_priority AS "completed_or_priority",
    NOT is_completed AS "not_completed"
FROM 
    task_list;

Result:

         task_name         | is_completed | is_priority | completed_priority | completed_or_priority | not_completed 
---------------------------+--------------+-------------+--------------------+----------------------+---------------
 Complete PostgreSQL tutorial | f            | t           | f                  | t                    | t
 Buy groceries              | f            | t           | f                  | t                    | t
 Pay electricity bill       | f            | t           | f                  | t                    | t
 Call dentist               | f            | t           | f                  | t                    | t
 Submit project proposal    | f            | NULL        | f                  | NULL                 | t

Boolean Logic with NULL Values

When working with Boolean values, it's important to understand how NULL affects operations:

• TRUE AND NULL results in NULL
• FALSE AND NULL results in FALSE
• TRUE OR NULL results in TRUE
• FALSE OR NULL results in NULL
• NOT NULL results in NULL

This follows the three-valued logic of SQL where NULL represents an unknown state.

Using Boolean Values in WHERE Clauses

Boolean columns can be used directly in WHERE clauses for filtering:

sql

-- Find all priority tasks
SELECT task_name FROM task_list WHERE is_priority;

-- Find all non-priority tasks (including NULL values)
SELECT task_name FROM task_list WHERE NOT is_priority;

-- Find tasks that are neither completed nor priority
SELECT task_name FROM task_list WHERE NOT (is_completed OR is_priority);

-- Find all tasks with unknown priority
SELECT task_name FROM task_list WHERE is_priority IS NULL;

A key point to remember: When filtering with Boolean columns, you don't need to compare them explicitly with TRUE or FALSE. The expression WHERE is_completed is equivalent to WHERE is_completed = TRUE.

Practical Examples

Example 1: Filtering User Preferences

Imagine we have a user settings table:

sql

CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    receive_notifications BOOLEAN DEFAULT TRUE,
    dark_mode_enabled BOOLEAN DEFAULT FALSE,
    public_profile BOOLEAN DEFAULT TRUE
);

INSERT INTO user_settings (user_id, username, receive_notifications, dark_mode_enabled, public_profile)
VALUES 
    (1, 'john_doe', TRUE, TRUE, FALSE),
    (2, 'jane_smith', FALSE, TRUE, TRUE),
    (3, 'bob_johnson', TRUE, FALSE, NULL);

To find users who want to receive notifications and have dark mode enabled:

sql

SELECT username 
FROM user_settings 
WHERE receive_notifications AND dark_mode_enabled;

Result:

 username 
----------
 john_doe

Example 2: Status Tracking System

Let's create a more complex example of a shipment tracking system:

sql

CREATE TABLE shipments (
    shipment_id SERIAL PRIMARY KEY,
    tracking_number VARCHAR(50) UNIQUE,
    is_international BOOLEAN,
    customs_cleared BOOLEAN,
    is_delivered BOOLEAN DEFAULT FALSE,
    is_express BOOLEAN,
    delivery_date DATE
);

INSERT INTO shipments (tracking_number, is_international, customs_cleared, is_delivered, is_express, delivery_date)
VALUES
    ('TN12345', TRUE, TRUE, TRUE, TRUE, '2023-05-15'),
    ('TN67890', TRUE, FALSE, FALSE, FALSE, NULL),
    ('TN24680', FALSE, NULL, FALSE, TRUE, NULL),
    ('TN13579', TRUE, TRUE, FALSE, TRUE, NULL),
    ('TN97531', FALSE, NULL, TRUE, FALSE, '2023-05-10');

Find all international shipments that either haven't cleared customs or haven't been delivered:

sql

SELECT 
    tracking_number,
    CASE 
        WHEN NOT customs_cleared THEN 'Awaiting customs'
        WHEN NOT is_delivered THEN 'In transit'
        ELSE 'Delivered'
    END AS status
FROM shipments
WHERE is_international AND (NOT customs_cleared OR NOT is_delivered);

Result:

 tracking_number |      status      
-----------------+------------------
 TN67890         | Awaiting customs
 TN13579         | In transit

Boolean Functions and Expressions

PostgreSQL provides several functions for working with Boolean values:

COALESCE Function

The COALESCE function returns the first non-NULL value in a list, which is useful for handling NULLs in Boolean operations:

sql

SELECT 
    task_name,
    is_priority,
    COALESCE(is_priority, FALSE) AS priority_or_false
FROM 
    task_list;

Result:

         task_name         | is_priority | priority_or_false 
---------------------------+-------------+-------------------
 Complete PostgreSQL tutorial | t           | t
 Buy groceries              | t           | t
 Pay electricity bill       | t           | t
 Call dentist               | t           | t
 Submit project proposal    | NULL        | f

Boolean Expressions

You can create complex Boolean expressions in PostgreSQL:

sql

-- Expression to categorize tasks
SELECT 
    task_name,
    CASE 
        WHEN is_completed THEN 'Done'
        WHEN is_priority THEN 'Urgent'
        ELSE 'Normal'
    END AS status
FROM 
    task_list;

Result:

         task_name         | status 
---------------------------+--------
 Complete PostgreSQL tutorial | Urgent
 Buy groceries              | Urgent
 Pay electricity bill       | Urgent
 Call dentist               | Urgent
 Submit project proposal    | Normal

Converting Between Boolean and Other Types

PostgreSQL allows conversion between Boolean and other data types:

Boolean to Text

sql

SELECT 
    is_completed,
    is_completed::TEXT
FROM 
    task_list
LIMIT 1;

Result:

 is_completed | is_completed 
--------------+--------------
 f            | false

Integer to Boolean

sql

SELECT 
    0::BOOLEAN,
    1::BOOLEAN;

Result:

 bool | bool 
------+------
 f    | t

Performance Considerations

Boolean columns are very storage-efficient in PostgreSQL, requiring only 1 byte per value. This makes them ideal for storing flags and states that have binary outcomes.

For tables with millions of rows, using Boolean types instead of character or integer types to represent binary states can save significant storage space.

Best Practices

1. Use Descriptive Column Names: Prefix with is_, has_, or can_ to make their purpose clear (e.g., is_active, has_subscription, can_edit).

2. Consider NOT NULL Constraints: Unless you have a specific reason to allow NULL values in Boolean columns, it's often clearer to use NOT NULL constraints with DEFAULT values.

   sql

   CREATE TABLE accounts (
       account_id SERIAL PRIMARY KEY,
       email VARCHAR(100) UNIQUE NOT NULL,
       is_verified BOOLEAN NOT NULL DEFAULT FALSE,
       is_admin BOOLEAN NOT NULL DEFAULT FALSE
   );

3. Avoid Redundant Comparisons: Instead of WHERE is_active = TRUE, simply use WHERE is_active.

4. Be Careful with NOT Operator: Remember that NOT is_active will not match rows where is_active is NULL. If you want to include NULLs, use WHERE is_active IS NOT TRUE.

Summary

The PostgreSQL Boolean type is a powerful tool for representing binary states and logical conditions in your database:

• Boolean columns can store TRUE, FALSE, or NULL values
• PostgreSQL accepts various input formats for Boolean values
• Boolean operators (AND, OR, NOT) follow standard logical rules
• NULL values in Boolean operations follow three-valued logic
• Boolean columns can be used directly in WHERE clauses
• Boolean types are storage-efficient and semantically clear

In database design, using Boolean columns appropriately can lead to more intuitive data models, more readable queries, and better performance.

Exercises

1. Create a table called product_inventory with columns for product name, price, and Boolean columns for in_stock, is_featured, and is_on_sale.

2. Insert five products with different combinations of Boolean values.

3. Write a query to find all products that are either featured or on sale, but not both.

4. Write a query to categorize products based on their Boolean attributes into "Hot Items" (featured and on sale), "Regular Stock" (in stock but not featured or on sale), and "Out of Stock" (not in stock).

Additional Resources

• PostgreSQL Official Documentation on Boolean Type
• PostgreSQL Logical Operators
• For practice on Boolean operations, try the logical puzzle exercises on platforms like LeetCode or HackerRank.