Boolean type behavior in PostgreSQL

Introduction

Boolean type helps store true or false values simply and clearly in a database.

To track if a user is active or inactive in an app.

To mark if a task is completed or not.

To store yes/no answers in surveys.

To control feature flags that turn features on or off.

To check if a product is in stock or out of stock.

Syntax

PostgreSQL

column_name BOOLEAN

Boolean columns store only TRUE, FALSE, or NULL values.

You can use TRUE/FALSE keywords or 1/0 in some cases.

Examples

Creates a table with a boolean column to track if users are active.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  is_active BOOLEAN
);

Inserts true, false, and unknown (null) values into the boolean column.

PostgreSQL

INSERT INTO users (is_active) VALUES (TRUE), (FALSE), (NULL);

Selects only users who are active.

PostgreSQL

SELECT * FROM users WHERE is_active = TRUE;

Sample Program

This creates a temporary tasks table with a boolean column to mark if tasks are done. It inserts three tasks with different boolean states and then selects all rows.

PostgreSQL

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

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

SELECT id, description, is_done FROM tasks ORDER BY id;

Important Notes

In PostgreSQL, boolean TRUE is shown as 't' and FALSE as 'f' in query results.

NULL means the value is unknown or not set.

You can use boolean expressions in WHERE clauses for filtering.

Summary

Boolean type stores true, false, or unknown values.

Use boolean columns to represent yes/no or on/off states.

Boolean values help write clear and simple queries.