Boolean Data Type: BOOLEAN

You’ve already checked out data types that store numbers and text. Now it’s time to talk about the boolean data type — BOOLEAN. Honestly, this is probably the most “straightforward” data type, since it only answers two questions: “Yes” or “No.” And sometimes — “Maybe” (read: NULL).

The boolean data type BOOLEAN stores values that can only be:

• TRUE (true);
• FALSE (false);
• NULL (no value).

Imagine a light switch: it can be on TRUE, off FALSE, or in an unknown state NULL if the bulb just disappeared.

You could say the boolean data type is the foundation for decision-making and conditional logic in SQL. Without it, you couldn’t build a proper filter in a query or mark the status of something.

Use Cases

The boolean data type is used all over the place in databases. Here are a few real-life examples:

Flags and statuses.

For example, the is_active field can show if a user is active (TRUE) or not (FALSE).

SELECT username, is_active
FROM users 
WHERE is_active = TRUE;

Completion control.

For example, the is_complete field can store info about whether a task is finished.

SELECT task_name
FROM tasks 
WHERE is_complete = FALSE;

Availability check.

The is_available field can show if an item is in stock.

How to use BOOLEAN in PostgreSQL?

The boolean data type in PostgreSQL is just called BOOLEAN. Let’s create a table and add a few columns:

• Unique task identifier
• Task name
• Boolean status for task completion

Now let’s add a few records to the table:

Getting Data

Now let’s see how to fetch data based on a boolean value. For example, to get only incomplete tasks, write:

SELECT task_name
FROM tasks 
WHERE is_complete = FALSE;

This query will return only those tasks where is_complete is FALSE.

Result:

Features of working with BOOLEAN

Filtering values

To work with the boolean type, you can use the following syntax:

• = TRUE — if you want to explicitly say the value is true.
• = FALSE — if you want to select false values.
• IS NULL — if you want to select records with no value.

Example:

SELECT task_name
FROM tasks 
WHERE is_complete IS NULL;

Simplifying conditions

PostgreSQL is so “smart” that you often don’t even need to write = TRUE explicitly. For example:

SELECT task_name
FROM tasks 
WHERE is_complete;

This query is the same as WHERE is_complete = TRUE.

Common mistakes when working with BOOLEAN

When you’re just starting out with a new data type, mistakes are inevitable. Here are a few common problems you might run into:

Comparing with NULL. Beginners often write is_active = NULL. But you can’t compare NULL with =. To check if a value is NULL, use IS NULL or IS NOT NULL.

Explicitly writing = TRUE. Sometimes devs add extra stuff like WHERE is_active = TRUE. It’s not an error, but you can make it simpler by just writing WHERE is_active.

String literals vs keywords. PostgreSQL is happy to coerce string literals like 'true'/'false'/'yes'/'no'/'on'/'off'/'1'/'0'/'t'/'f' into BOOLEAN (so is_active = 'TRUE' actually does work), but it’s better to use the keywords TRUE/FALSE/NULL without quotes — it’s unambiguous and reads cleaner.

Why bother?

You might be wondering: why make life harder by adding this BOOLEAN? Why not just use numbers, where 1 is true and 0 is false?

The answer’s simple: BOOLEAN makes your code and data way more expressive. When someone looks at a table with an is_active field, it’s instantly clear that this field stores a logical thing (yes/no). It makes your database schema easier to read and your data model more understandable for other devs.