Boolean column filtering patterns in PostgreSQL

Introduction

Boolean columns store true or false values. Filtering by these helps find rows that match a yes/no condition.

Finding all active users where active = true

Selecting orders that are marked as shipped (shipped = true)

Filtering products that are on sale (on_sale = true)

Getting records where a feature is disabled (enabled = false)

Checking if a task is completed (completed = true)

Syntax

PostgreSQL

SELECT * FROM table_name WHERE boolean_column = TRUE;

-- or

SELECT * FROM table_name WHERE boolean_column = FALSE;

You can write WHERE boolean_column to filter for true values.

Use WHERE NOT boolean_column to filter for false values.

Examples

Get all users where the active column is true.

PostgreSQL

SELECT * FROM users WHERE active = TRUE;

Get all orders that are not shipped.

PostgreSQL

SELECT * FROM orders WHERE shipped = FALSE;

Shortcut to get products where on_sale is true.

PostgreSQL

SELECT * FROM products WHERE on_sale;

Get tasks that are not completed (completed = false).

PostgreSQL

SELECT * FROM tasks WHERE NOT completed;

Sample Program

This creates a temporary tasks table, inserts three tasks with true/false completed status, then selects only the completed tasks.

PostgreSQL

CREATE TEMP TABLE tasks (id SERIAL PRIMARY KEY, description TEXT, completed BOOLEAN);

INSERT INTO tasks (description, completed) VALUES
('Wash dishes', TRUE),
('Do homework', FALSE),
('Read book', TRUE);

SELECT id, description FROM tasks WHERE completed = TRUE ORDER BY id;

Important Notes

Boolean columns only store TRUE, FALSE, or NULL (unknown).

Be careful with NULL values; filtering with = TRUE excludes NULLs.

Use IS TRUE or IS FALSE to handle NULLs explicitly.

Summary

Boolean filtering helps select rows based on yes/no conditions.

Use WHERE column = TRUE or simply WHERE column for true values.

Use WHERE column = FALSE or WHERE NOT column for false values.

Practice

1. Which SQL condition correctly filters rows where a boolean column is_active is true?

easy

A. WHERE NOT is_active

B. WHERE is_active = FALSE

C. WHERE is_active = NULL

D. WHERE is_active = TRUE

Solution

1. Step 1: Understand boolean filtering for true values

   To filter rows where a boolean column is true, you can use WHERE column = TRUE or simply WHERE column.

2. Step 2: Evaluate each option

   WHERE is_active = TRUE uses WHERE is_active = TRUE, which is correct. WHERE is_active = FALSE filters false values. WHERE NOT is_active filters false values by negation. WHERE is_active = NULL compares to NULL, which is invalid for boolean filtering.

3. Final Answer:

   WHERE is_active = TRUE -> Option D

4. Quick Check:

   Boolean true filter = WHERE column = TRUE [OK]

Hint: Use WHERE column = TRUE or just WHERE column for true [OK]

Common Mistakes:

• Using WHERE column = FALSE to find true rows
• Using WHERE NOT column to find true rows
• Comparing boolean to NULL

2. Which of the following is the correct syntax to filter rows where the boolean column is_verified is false in PostgreSQL?

easy

A. WHERE is_verified = TRUE

B. WHERE is_verified == FALSE

C. WHERE NOT is_verified

D. WHERE is_verified = NULL

Solution

1. Step 1: Recall boolean false filtering syntax

   To filter false boolean values, use WHERE column = FALSE or WHERE NOT column.

2. Step 2: Check each option's syntax

   WHERE NOT is_verified uses WHERE NOT is_verified, which is valid and filters false values. WHERE is_verified = TRUE filters true values. WHERE is_verified == FALSE uses double equals which is invalid in SQL. WHERE is_verified = NULL compares to NULL, which is incorrect.

3. Final Answer:

   WHERE NOT is_verified -> Option C

4. Quick Check:

   Boolean false filter = WHERE NOT column [OK]

Hint: Use WHERE NOT column or WHERE column = FALSE for false [OK]

Common Mistakes:

• Using == instead of = in SQL
• Comparing boolean to NULL
• Using WHERE column = TRUE to find false rows

3. Given the table users with a boolean column is_admin, what will the following query return?

SELECT username FROM users WHERE is_admin;

medium

A. All usernames where is_admin is true

B. All usernames where is_admin is false

C. All usernames regardless of is_admin value

D. Syntax error due to missing comparison

Solution

1. Step 1: Understand filtering by boolean column directly

   In PostgreSQL, using WHERE is_admin filters rows where is_admin is true.

2. Step 2: Analyze the query result

   The query selects usernames only where is_admin is true, so it returns those usernames.

3. Final Answer:

   All usernames where is_admin is true -> Option A

4. Quick Check:

   WHERE boolean_column filters true rows [OK]

Hint: WHERE boolean_column filters true rows directly [OK]

Common Mistakes:

• Thinking WHERE boolean_column filters false rows
• Expecting syntax error without explicit comparison
• Assuming it returns all rows

4. Identify the error in this query that tries to select rows where is_active is false:

SELECT * FROM accounts WHERE is_active = FALSE;

medium

A. No error, query is correct

B. Should use WHERE NOT is_active instead

C. Boolean columns cannot be compared with FALSE

D. Missing parentheses around FALSE

Solution

1. Step 1: Check boolean comparison syntax

   In PostgreSQL, comparing a boolean column with FALSE using = FALSE is valid syntax.

2. Step 2: Confirm query correctness

   The query correctly filters rows where is_active is false. Using WHERE NOT is_active is an alternative but not required.

3. Final Answer:

   No error, query is correct -> Option A

4. Quick Check:

   Boolean = FALSE is valid syntax [OK]

Hint: Boolean = FALSE is valid; NOT is optional [OK]

Common Mistakes:

• Thinking boolean cannot be compared with FALSE
• Believing parentheses are needed around FALSE
• Confusing NOT with = FALSE as mandatory

5. You want to select all rows from orders where the boolean column shipped is false or NULL. Which query correctly achieves this?

hard

A. SELECT * FROM orders WHERE NOT shipped;

B. SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL;

C. SELECT * FROM orders WHERE shipped = FALSE;

D. SELECT * FROM orders WHERE shipped IS NULL;

Solution

1. Step 1: Understand boolean and NULL filtering

   Boolean columns can be true, false, or NULL. To include false or NULL, both conditions must be checked.

2. Step 2: Analyze each option

   SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL; explicitly checks for false or NULL, correctly including both. SELECT * FROM orders WHERE NOT shipped; filters only false but excludes NULL. SELECT * FROM orders WHERE shipped = FALSE; excludes NULL. SELECT * FROM orders WHERE shipped IS NULL; filters only NULL, missing false rows.

3. Final Answer:

   SELECT * FROM orders WHERE shipped = FALSE OR shipped IS NULL; -> Option B

4. Quick Check:

   Include false and NULL with OR condition [OK]

Hint: Use OR with IS NULL to include false or NULL [OK]

Common Mistakes:

• Using NOT shipped excludes NULL rows
• Ignoring NULL values in boolean filtering
• Assuming shipped = FALSE includes NULL