Boolean

PostgreSQL provides a single BOOLEAN data type (abbreviated BOOL) that can hold three states: true, false, and NULL. It uses 1 byte of storage and is the standard choice for flag columns, feature toggles, and any binary yes/no attribute.

Accepted boolean literal values

PostgreSQL accepts a wide range of input literals for boolean columns. All non-keyword literals must be quoted:

• True: true, 't', 'true', 'y', 'yes', '1'
• False: false, 'f', 'false', 'n', 'no', '0'

Leading and trailing whitespace is ignored for quoted values.

Creating and querying a boolean column

CREATE TABLE stock_availability (
  product_id INT PRIMARY KEY,
  available  BOOLEAN NOT NULL
);

Insert rows using various accepted literals:

INSERT INTO stock_availability (product_id, available)
VALUES
  (100, TRUE),
  (200, FALSE),
  (300, 't'),
  (400, '1'),
  (500, 'yes'),
  (700, 'no'),
  (800, '0');

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

SELECT * FROM stock_availability WHERE available;

product_id | available
------------+-----------
       100 | t
       300 | t
       400 | t
       500 | t

Find unavailable products using NOT:

SELECT * FROM stock_availability WHERE NOT available;

product_id | available
------------+-----------
       200 | f
       700 | f
       800 | f

Setting default values for boolean columns

Set a default on an existing table using ALTER TABLE:

ALTER TABLE stock_availability
ALTER COLUMN available SET DEFAULT FALSE;

Now inserting without specifying available defaults to false:

INSERT INTO stock_availability (product_id) VALUES (900);

SELECT * FROM stock_availability WHERE product_id = 900;

product_id | available
------------+-----------
       900 | f

For new tables, add the default inline in the column definition:

CREATE TABLE feature_flags (
  flag_name VARCHAR(100) PRIMARY KEY,
  is_enabled BOOL DEFAULT false
);

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 specify NOT NULL with a DEFAULT for flag columns to prevent three-valued logic surprises with NULL in WHERE conditions.
• Partial indexes on boolean columns are highly efficient: CREATE INDEX ON users (id) WHERE is_active indexes only active rows.
• Avoid storing boolean values as integers (0/1) in PostgreSQL — the native BOOLEAN type is more readable and type-safe.

Reference: PostgreSQL documentation — Boolean Type.