Postgres Boolean Type: Values, NULL Logic and Counting

TL;DR: The PostgreSQL boolean type stores true, false or NULL (unknown) in 1 byte. It accepts true/yes/on/1 and false/no/off/0 as input, always prints t or f, and the cleanest way to count true values is count(*) FILTER (WHERE flag).

A Postgres boolean is the standard SQL boolean type (alias bool) with three possible states: true, false, and a third "unknown" state represented by SQL NULL. Each value takes 1 byte of storage. When you write data, Postgres accepts the keywords TRUE and FALSE plus string forms such as 'yes', 'on', '1', 't' and 'n', case-insensitive and with surrounding whitespace ignored. When you read data, psql always shows t or f. Because NULL is a real third state, filters like WHERE NOT is_active silently skip NULL rows, which is the most common bug with this type.

PostgreSQL boolean quick reference

Source: PostgreSQL 18 docs, Boolean Type and Aggregate Functions.

What values does Postgres accept for boolean?

Postgres accepts the SQL keywords TRUE and FALSE, and any of these strings, in any case:

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  is_active  boolean NOT NULL DEFAULT true,
  is_trial   boolean            -- nullable on purpose: NULL = not decided yet
);

INSERT INTO users (email, is_active, is_trial) VALUES
  ('ana@example.com',  TRUE,    'yes'),
  ('ben@example.com',  'off',   '0'),
  ('cara@example.com', ' T ',   NULL),
  ('dev@example.com',  'n',     'on');

SELECT email, is_active, is_trial FROM users;
--       email       | is_active | is_trial
-- ------------------+-----------+----------
--  ana@example.com  | t         | t
--  ben@example.com  | f         | f
--  cara@example.com | t         |
--  dev@example.com  | f         | t

Unique prefixes work, so 't', 'tr', 'y' and 'n' are all valid. An ambiguous prefix fails: 'o' could be on or off, so SELECT 'o'::boolean; raises invalid input syntax for type boolean: "o".

The docs call TRUE and FALSE the preferred, SQL-compliant way to write constants. Use the string forms only when the value comes from outside, such as a CSV import where a column holds yes/no.

Can you insert 1 and 0 into a boolean column?

Yes as strings, not as bare integers. '1' and '0' are valid boolean input. A bare integer is a different type, and the integer-to-boolean cast is explicit only, so this fails:

INSERT INTO users (email, is_active) VALUES ('eve@example.com', 1);
-- ERROR:  column "is_active" is of type boolean but expression is of type integer
-- HINT:  You will need to rewrite or cast the expression.

-- Works:
INSERT INTO users (email, is_active) VALUES ('eve@example.com', '1');
INSERT INTO users (email, is_active) VALUES ('eve@example.com', 1::boolean);

With an explicit cast, 0 becomes false and any other integer becomes true. Going the other way, true::int returns 1 and false::int returns 0. For a full list of cast rules see our PostgreSQL CAST guide.

How much space does a boolean take?

A boolean takes 1 byte, per the PostgreSQL boolean type docs. Compare that to the alternatives people use to store flags:

In practice, row alignment padding often hides the 1-byte difference, so pick boolean for meaning, not to save disk. If you have many flags on a wide table, group the boolean columns next to each other so they pack together.

How does NULL work with boolean? (three-valued logic)

SQL uses three-valued logic: true, false and NULL meaning unknown. AND, OR and NOT follow these rules from the logical operators docs:

NOT NULL (the expression, not the constraint) is NULL. A WHERE clause keeps only rows where the condition is true, so both of these skip cara@example.com, whose is_trial is NULL:

SELECT email FROM users WHERE is_trial;        -- ana, dev
SELECT email FROM users WHERE NOT is_trial;    -- ben only, cara is missing

IS TRUE, IS NOT TRUE and IS UNKNOWN

The IS predicates never return NULL, which makes them the safe choice when a column is nullable (comparison predicates docs):

SELECT email FROM users WHERE is_trial IS NOT TRUE;  -- ben, cara (false or NULL)
SELECT email FROM users WHERE is_trial IS NOT FALSE; -- ana, cara, dev (true or NULL)
SELECT email FROM users WHERE is_trial IS UNKNOWN;   -- cara (same as IS NULL for boolean)

If NULL has no business meaning in your table, declare the column boolean NOT NULL DEFAULT false and the problem goes away. If you need a fallback at read time, COALESCE(is_trial, false) works too.

How do I count true values?

Use the aggregate FILTER clause (PostgreSQL 9.4 and later). It is the clearest form and handles NULL correctly:

SELECT
  count(*)                                AS total_users,
  count(*) FILTER (WHERE is_active)       AS active_users,
  count(*) FILTER (WHERE NOT is_active)   AS inactive_users,
  count(*) FILTER (WHERE is_trial IS NULL) AS trial_unknown
FROM users;

Other forms you will see, and how they differ:

-- Sum of a cast: works, but returns NULL (not 0) when every row is NULL
SELECT sum(is_active::int) FROM users;

-- CASE inside count: count() ignores NULLs, so the ELSE must be absent
SELECT count(CASE WHEN is_active THEN 1 END) FROM users;

-- WRONG: counts every non-NULL row, true AND false
SELECT count(is_active) FROM users;

The last one is a classic mistake. count(expression) counts non-NULL values, and false is not NULL. Our SQL COUNTIF guide covers the same pattern across MySQL, SQL Server and BigQuery, and PostgreSQL CASE WHEN covers conditional logic in more depth.

Percent of rows that are true

SELECT round(100.0 * count(*) FILTER (WHERE is_active) / count(*), 1) AS pct_active
FROM users;

Or the shorter avg(is_active::int), which returns a fraction between 0 and 1 and ignores NULL rows.

bool_and, bool_or and every

These aggregates answer "are all of them true?" and "is any of them true?" per group:

SELECT account_id,
       bool_and(is_active) AS all_seats_active,
       bool_or(is_trial)   AS any_seat_on_trial
FROM seats
GROUP BY account_id;

bool_and returns true if all non-null inputs are true; bool_or returns true if any non-null input is true. every() is the SQL-standard name for bool_and. All three ignore NULL inputs and return NULL only when every input is NULL.

Sorting and indexing boolean columns

ORDER BY flag puts false before true, and NULLs last in ascending order (first in DESC). Add NULLS FIRST or NULLS LAST to control it.

A plain B-tree index on a boolean column rarely helps, because half the table matches either value. When one value is rare, a partial index is the better tool:

-- 2% of orders need review: index only those rows
CREATE INDEX orders_needs_review_idx ON orders (created_at) WHERE needs_review;

SELECT id, created_at FROM orders WHERE needs_review ORDER BY created_at DESC LIMIT 50;

Write the query's condition exactly like the index predicate (WHERE needs_review) so the planner can match it.

Boolean vs bit vs smallint

Use boolean for yes/no facts. bit(1) is a bit string type meant for bitwise operations, and it does not work with AND/OR or in a WHERE clause without a comparison. smallint flags come from MySQL habits (where BOOL is a synonym for TINYINT(1)) and allow values other than 0 and 1. If you are porting from MySQL, convert them with ALTER TABLE t ALTER COLUMN flag TYPE boolean USING flag <> 0;. Our Postgres vs MySQL comparison covers other type differences like this one.

Turning boolean flags into a report

Boolean columns like is_active, is_trial and churned are where most SaaS reporting questions start: how many active accounts this week, which trial users never converted, which share of signups verified their email. If you would rather ask that in plain English than hand-write the FILTER clauses, try AI for Database free at aifordatabase.com. It writes and runs the query against your PostgreSQL database, shows the SQL it used, and can keep the number on a dashboard that refreshes itself.