Boolean Data Type

Introduction to Boolean Type

PostgreSQL has a native BOOLEAN data type.

It accepts three values:

• TRUE
• FALSE
• NULL (unknown)

Aliases:

You can also use:

• 't', 'true', 'y', 'yes', '1' → interpreted as TRUE
• 'f', 'false', 'n', 'no', '0' → interpreted as FALSE

Creating a Boolean Column

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  is_done BOOLEAN DEFAULT FALSE
);

INSERT INTO tasks (title, is_done) VALUES
  ('Buy groceries', TRUE),
  ('Walk the dog', FALSE),
  ('Pay bills', NULL); -- Unknown status

Querying Booleans

-- All completed tasks
SELECT * FROM tasks WHERE is_done = TRUE;

-- Tasks that are not completed
SELECT * FROM tasks WHERE is_done = FALSE;

-- Tasks with unknown completion status
SELECT * FROM tasks WHERE is_done IS NULL;

Boolean Expressions

Basic Expressions

SELECT 5 > 3; -- true
SELECT 2 = 4; -- false

Used in Conditional Logic

SELECT *,
  CASE
    WHEN is_done THEN 'Completed'
    WHEN NOT is_done THEN 'Pending'
    ELSE 'Unknown'
  END AS status
FROM tasks;

Working with NULL and Three-Valued Logic

BOOLEAN in PostgreSQL supports three-valued logic:

• TRUE
• FALSE
• UNKNOWN (NULL)

This matters in filters:

-- This won't include NULLs!
SELECT * FROM tasks WHERE NOT is_done;

-- Include unknown explicitly
SELECT * FROM tasks WHERE is_done = FALSE OR is_done IS NULL;

Common Use Cases

Flags or Switches

ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT FALSE;

Feature Toggles

CREATE TABLE features (
  name TEXT PRIMARY KEY,
  is_active BOOLEAN NOT NULL
);

Constraints with Boolean

Ensure a column is always true:

ALTER TABLE users
  ADD CONSTRAINT must_be_verified CHECK (is_verified = TRUE);

Dummy Dataset Example

CREATE TABLE users (
  id SERIAL,
  name TEXT,
  is_active BOOLEAN
);

INSERT INTO users (name, is_active) VALUES
  ('Alice', TRUE),
  ('Bob', FALSE),
  ('Charlie', NULL);

SELECT * FROM users WHERE is_active;
SELECT * FROM users WHERE NOT is_active;
SELECT * FROM users WHERE is_active IS NULL;

When NOT to Use Boolean

Alternatives to Boolean

• If a flag can have more than two options (e.g., active, inactive, banned) → use ENUM or TEXT
• If storing numbers like 1/0 for analytics → use SMALLINT instead

Avoid multiple BOOLEAN columns for status. Use one ENUM or TEXT for clarity.