Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 90 ยท Data Types

Booleans and Enums

Use boolean columns correctly, understand three-valued logic with NULL, and decide between CREATE TYPE AS ENUM, CHECK constraints and lookup tables in PostgreSQL.

A surprising number of columns hold one of a few fixed answers: is the account active, is the order paid, is the ticket open, in progress or closed. PostgreSQL offers a real boolean type for yes or no, and three ways to model a small set of named states: an ENUM type, a CHECK constraint on text, or a lookup table with a foreign key.

This lesson covers all four tools and when each fits. The boolean rules and the CHECK and lookup designs run live in the site's SQLite engine. CREATE TYPE ... AS ENUM is PostgreSQL-only, so those blocks are read-only and labeled.

Modeling a fixed set of states

boolean Exactly two answers, plus NULL.
ENUM type A named list stored as a compact type.
CHECK Text column limited to allowed values.
Lookup table States live as rows with a foreign key.

Pick by how often the list changes and how much data the states carry.

1 / 2

The boolean type

A boolean column holds true, false or NULL (unknown). It takes one byte. PostgreSQL accepts several spellings on input and always prints t or f in psql:

SQL
-- Read-only: PostgreSQL
SELECT true, false, NULL::boolean;

-- These all mean true: TRUE, 't', 'true', 'y', 'yes', 'on', '1'
-- These all mean false: FALSE, 'f', 'false', 'n', 'no', 'off', '0'
CREATE TABLE accounts (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     text    NOT NULL,
  is_active boolean NOT NULL DEFAULT true
);

SELECT email FROM accounts WHERE is_active;        -- no "= true" needed
SELECT email FROM accounts WHERE NOT is_active;

Because a boolean is already a truth value, write WHERE is_active rather than WHERE is_active = true. Name boolean columns like questions or states, such as is_active, has_paid or email_verified.

Booleans in SQLite

SQLite has no boolean storage class. Since version 3.23 the keywords TRUE and FALSE are aliases for the integers 1 and 0, and conditions evaluate to 1, 0 or NULL. That is enough to demonstrate the logic.

SQL Truth values and NULL

Press Run (or Ctrl + Enter) to see the result here.

The comparison with NULL returns NULL (shown as an empty or null cell), not false. That leads to the central idea of this lesson.

Three-valued logic

SQL has three truth values: true, false and unknown (NULL). Any comparison with NULL is unknown. WHERE keeps a row only when its condition is true; both false and unknown rows are dropped. This surprises people:

a b a AND b a OR b
true NULL NULL true
false NULL false NULL
NULL NULL NULL NULL

The rule of thumb: false AND anything is false, and true OR anything is true, because the unknown value cannot change the result. In every other combination the unknown value leaks through.

SQL AND and OR with unknown values

Press Run (or Ctrl + Enter) to see the result here.

To test for the unknown value you need special operators. PostgreSQL has the standard ones plus the boolean tests:

Expression Result for NULL input
x IS NULL true
x IS TRUE false
x IS NOT TRUE true (so it catches both false and unknown)
x IS DISTINCT FROM y like <> but treats two NULLs as equal and never returns NULL
SQL
-- Read-only: PostgreSQL
-- Find every account that is NOT confirmed active, including those with NULL:
SELECT email FROM accounts WHERE is_active IS NOT TRUE;

-- NULL-safe comparison:
SELECT 1 WHERE NULL IS NOT DISTINCT FROM NULL;   -- returns a row

SQLite spells the NULL-safe comparison IS and IS NOT: NULL IS NULL is 1, and 1 IS NOT 2 is 1. PostgreSQL's IS DISTINCT FROM does the same job. See NULL for the portable basics.

Make boolean columns NOT NULL with a default whenever the question has only two real answers. A nullable boolean has three states, which almost nobody intends.

Enums

An enum is a type you create with a fixed, ordered list of labels. Once defined it works like any other column type:

SQL
-- Read-only: PostgreSQL
CREATE TYPE ticket_status AS ENUM ('open', 'in_progress', 'closed');

CREATE TABLE tickets (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  text NOT NULL,
  status ticket_status NOT NULL DEFAULT 'open'
);

INSERT INTO tickets (title) VALUES ('Printer is on fire');
INSERT INTO tickets (title, status) VALUES ('Reset password', 'closed');

-- INSERT ... VALUES ('x', 'resolved') fails:
-- ERROR: invalid input value for enum ticket_status: "resolved"

Enum values take 4 bytes each, regardless of label length, and they compare quickly. An invalid label is rejected at insert time, so the database guards the data.

Ordering

Enum values sort by the order in which they were declared, not alphabetically. That makes range comparisons meaningful:

SQL
-- Read-only: PostgreSQL
SELECT title FROM tickets ORDER BY status;          -- open, then in_progress, then closed
SELECT title FROM tickets WHERE status < 'closed';  -- open and in_progress
SELECT enum_range(NULL::ticket_status);             -- {open,in_progress,closed}

Changing an enum

You can add values, but you cannot remove them:

SQL
-- Read-only: PostgreSQL
ALTER TYPE ticket_status ADD VALUE 'on_hold' AFTER 'in_progress';
ALTER TYPE ticket_status RENAME VALUE 'on_hold' TO 'waiting';

Adding a value is quick and does not rewrite the table. There are some limits: since PostgreSQL 12 it can run inside a transaction block, but the new value cannot be used until that transaction commits. Removing a value, or reordering the list, has no supported command. The workaround is to create a new type, migrate the column with ALTER COLUMN ... TYPE ... USING, and drop the old type, which is a heavy operation on large tables. That is the main argument against enums for lists that change often.

Alternative: CHECK constraints

A text column with a CHECK constraint gives a similar guarantee without a new type:

SQL
-- Read-only: PostgreSQL syntax
CREATE TABLE tickets (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  text NOT NULL,
  status text NOT NULL DEFAULT 'open'
         CHECK (status IN ('open', 'in_progress', 'closed'))
);

Changing the allowed list means dropping and re-adding the constraint (with NOT VALID then VALIDATE to avoid long locks, covered in Constraints in Depth). Sorting is alphabetical, not logical. The same design runs live in SQLite. This example uses INSERT OR IGNORE, which skips rows that break a CHECK instead of stopping with an error:

SQL A CHECK constraint as an enum

Press Run (or Ctrl + Enter) to see the result here.

Two rows remain. The 'resolved' row never entered the table, and ORDER BY status sorts text alphabetically (closed before open), unlike an enum.

Alternative: a lookup table

When the list changes often, carries extra data (a label, a color, a sort position), or is edited by non-developers, store it as rows and reference it with a foreign key:

SQL A lookup table for statuses

Press Run (or Ctrl + Enter) to see the result here.

The ordering comes from sort_order, and adding a new state is just an INSERT. In PostgreSQL the foreign key is always enforced. In SQLite it is enforced only after PRAGMA foreign_keys = ON, which is worth remembering when you test.

Which should you choose?

Need Best fit
Two answers boolean, NOT NULL
Small stable list, natural order, compact storage ENUM
Small list that may change, no extra data text with CHECK
List that changes often or carries labels and flags Lookup table with a foreign key

If you are unsure, a lookup table or a CHECK is the safer starting point, because they are easy to change. Reach for an enum when the list is truly stable, such as days of the week or fixed protocol states.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does WHERE is_active = NULL return in SQL?

    Comparing anything with NULL using = yields unknown, and WHERE keeps only rows whose condition is true. Use IS NULL instead.
  2. 2. Which expression is true when a boolean column is false or NULL?

    IS NOT TRUE covers both false and unknown. NOT col and col = false return unknown for NULL, so those rows would be dropped.
  3. 3. How do enum values sort in PostgreSQL?

    Enum values are ordered by their declaration position, so comparisons like status < 'closed' are meaningful.
  4. 4. Which change to an existing enum type is NOT supported by a simple command?

    You can add and rename values, but there is no command to remove one. You need to create a new type and migrate the column.

Summary

  • boolean holds true, false or NULL; write WHERE flag and make the column NOT NULL with a default.
  • SQL uses three-valued logic: comparisons with NULL are unknown, and WHERE keeps only true rows.
  • IS NOT TRUE, IS DISTINCT FROM and IS NULL handle the unknown value explicitly.
  • CREATE TYPE ... AS ENUM gives a compact, ordered, validated type; values can be added and renamed but not removed.
  • A CHECK constraint on text is flexible and easy to change; a lookup table with a foreign key suits lists that change or carry data.
  • Enum blocks here are read-only; the CHECK and lookup designs ran live in SQLite.

Next: Dates, Times and Time Zones.