Boolean Data Type in PostgreSQL

The PostgreSQL BOOLEAN data type (abbreviated BOOL) stores three-valued logic: true, false, and NULL. It is the standard choice for flag columns, feature toggles, and any binary yes/no attribute. One byte is used per stored value.

Syntax

column_name BOOLEAN
column_name BOOL   -- alias, identical behavior

PostgreSQL accepts a wide range of input literals. String literals must be quoted; only the bare keywords true and false are unquoted:

Practical Example

Create a product catalog with a boolean availability flag:

CREATE TABLE products (
  id        SERIAL PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  sku       VARCHAR(50)  NOT NULL UNIQUE,
  available BOOLEAN      NOT NULL DEFAULT true
);

INSERT INTO products (name, sku, available)
VALUES
  ('Titanium Mug',       'MUG-001', TRUE),
  ('Carbon Fiber Wallet','WAL-002', FALSE),
  ('Merino Wool Hoodie', 'HOD-003', 't'),
  ('Bamboo Desk Lamp',   'LMP-004', '1'),
  ('Recycled Notebook',  'NTB-005', 'yes'),
  ('Leather Keychain',   'KEY-006', '0');

Query available products using the column directly — no comparison operator needed:

SELECT name, sku FROM products WHERE available;

Find unavailable products with NOT:

SELECT name, sku FROM products WHERE NOT available;

Verify the stored format:

SELECT name, available FROM products ORDER BY id;

          name           | available
-------------------------+-----------
 Titanium Mug            | t
 Carbon Fiber Wallet     | f
 Merino Wool Hoodie      | t
 Bamboo Desk Lamp        | t
 Recycled Notebook       | t
 Leather Keychain        | f

Adding or Changing a Default Value

Set a DEFAULT on an existing table:

ALTER TABLE products
ALTER COLUMN available SET DEFAULT false;

Now new rows inserted without specifying available default to false:

INSERT INTO products (name, sku) VALUES ('Mystery Item', 'MST-007');

SELECT name, available FROM products WHERE sku = 'MST-007';

    name     | available
-------------+-----------
 Mystery Item | f

Partial Indexes on Boolean Columns

A partial index on a boolean column indexes only the matching subset of rows, making lookups on the filtered set extremely efficient:

-- Index only available products
CREATE INDEX idx_products_available ON products (id)
WHERE available = true;

-- Verify the index is used
EXPLAIN SELECT id, name FROM products WHERE available = true;

This index is far smaller than a full-table index and speeds up dashboard queries that frequently filter on availability.

Testing with Vela

Testing default value changes or flag migrations on a production table can be risky. With Vela’s database branching, you can create an instant copy-on-write clone of your production database, apply the ALTER TABLE and data migration on the branch, run your application test suite against the branch connection string, and only promote the change once you have confirmed correct behavior — all without touching the live database.

Production Tips

• Use the boolean column directly in WHERE clauses (WHERE is_active) rather than WHERE is_active = TRUE — it is shorter and equally readable.
• Always pair NOT NULL with a DEFAULT for flag columns to avoid three-valued logic surprises: a NULL boolean is neither true nor false and is excluded from both WHERE available and WHERE NOT available.
• Partial indexes on boolean columns are highly efficient — CREATE INDEX ON products (id) WHERE available indexes only active rows, keeping the index small.
• Avoid storing boolean values as integers in PostgreSQL — the native BOOLEAN type is more readable, type-safe, and integrates with WHERE col syntax.
• Inspect a table’s boolean columns with \d+ table_name in psql to verify defaults and constraints.