---
title: "Postgres Boolean Type: Values, NULL Logic and Counting"
description: "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…"
canonical: "https://www.aifordatabase.com/blog/postgres-boolean/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-06
updated: 2026-11-06
---

# 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

| Question | Answer |
| --- | --- |
| Type names | `boolean`, `bool` |
| Storage size | 1 byte |
| States | true, false, NULL (unknown) |
| Accepted true input | `TRUE`, `'true'`, `'yes'`, `'on'`, `'1'`, unique prefixes like `'t'`, `'y'` |
| Accepted false input | `FALSE`, `'false'`, `'no'`, `'off'`, `'0'`, unique prefixes like `'f'`, `'n'` |
| Output in psql | `t` or `f` (NULL shows as empty) |
| Preferred constants | `TRUE` and `FALSE` (SQL standard) |
| Sort order (ASC) | false, true, then NULL |
| Count true rows | `count(*) FILTER (WHERE flag)` |
| All true / any true | `bool_and(flag)` / `bool_or(flag)` |
| NULL-safe "not true" | `WHERE flag IS NOT TRUE` |

Source: [PostgreSQL 18 docs, Boolean Type](https://www.postgresql.org/docs/current/datatype-boolean.html) and [Aggregate Functions](https://www.postgresql.org/docs/current/functions-aggregate.html).

![Boolean input strings on the left all map to one of three stored states on the right](https://cdn.sanity.io/images/upsr56wu/production/664de4ae84f4840cc92ce0cf0593456588b17cb9-1344x410.png?w=1280)

FIG 01 — Case and surrounding spaces are ignored.

## 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](https://www.aifordatabase.com/blog/postgresql-cast/).

## How much space does a boolean take?

A boolean takes 1 byte, per the [PostgreSQL boolean type docs](https://www.postgresql.org/docs/current/datatype-boolean.html). Compare that to the alternatives people use to store flags:

| Type | Size | Notes |
| --- | --- | --- |
| `boolean` | 1 byte | three states with NULL; clear intent |
| `smallint` (0/1) | 2 bytes | allows 2, -1 and other junk unless you add a CHECK |
| `char(1)` ('Y'/'N') | 2 bytes for short values (1-byte header + 1) | needs a CHECK constraint |
| `bit(1)` | more than 1 byte (bit strings store a length header) | bitwise ops, not logical ones |

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](https://www.postgresql.org/docs/current/functions-logical.html):

| a | b | a AND b | a OR b |
| --- | --- | --- | --- |
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| TRUE | NULL | NULL | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| FALSE | NULL | FALSE | NULL |
| NULL | NULL | NULL | NULL |

`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
```

![WHERE NOT is_trial drops the NULL row because NOT NULL is NULL, not true](https://cdn.sanity.io/images/upsr56wu/production/981911a7c07a70dbb5ae42bbc035efd2e73a8e38-1344x486.png?w=1280)

FIG 02 — IS NOT TRUE treats unknown as not true.

### 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](https://www.postgresql.org/docs/current/functions-comparison.html)):

```
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](https://www.aifordatabase.com/blog/sql-countif/) covers the same pattern across MySQL, SQL Server and BigQuery, and [PostgreSQL CASE WHEN](https://www.aifordatabase.com/blog/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](https://www.postgresql.org/docs/current/indexes-partial.html) 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](https://www.aifordatabase.com/blog/why-is-postgres-better-than-mysql/) covers other type differences like this one.

VIDEO · [PostgreSQL - How to use the Boolean Data Type. Did you know there are 5 Ways to say TRUE or FALSE?](https://www.youtube.com/watch?v=ZFtHaIvc66c) (Software Nuggets)

## 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](https://www.aifordatabase.com/). It writes and runs the query against your [PostgreSQL database](https://www.aifordatabase.com/databases/postgresql/), shows the SQL it used, and can keep the number on a dashboard that refreshes itself.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### What is the difference between bool and boolean in Postgres?

None. bool is an alias for boolean; both create the same 1-byte type.

### Why does psql show t and f instead of true and false?

The boolean output function always emits t or f. Client libraries usually convert them: psycopg returns Python True/False, and node-postgres returns JavaScript booleans. To get words in SQL, use CASE WHEN flag THEN 'true' ELSE 'false' END or flag::text, which returns true or false.

### How do I set a default value for a boolean column?

Add DEFAULT: ALTER TABLE users ALTER COLUMN is\_active SET DEFAULT true;. To make it non-nullable too, backfill first with UPDATE users SET is\_active = true WHERE is\_active IS NULL;, then ALTER TABLE users ALTER COLUMN is\_active SET NOT NULL;.

### Is WHERE flag = true the same as WHERE flag?

Yes, both return only rows where the flag is true. WHERE flag is shorter and is what the docs use. Neither returns NULL rows.

### How do I toggle a boolean value?

UPDATE users SET is\_active = NOT is\_active WHERE id = 42;. A NULL stays NULL, because NOT NULL is NULL.

### Does PostgreSQL boolean work with JSON?

Yes. to\_jsonb(true) returns JSON true, and (data->>'verified')::boolean casts a JSON string field like "true" back to boolean.

Read next

*   [METRICFLOW RESOURCES: 18 DOCS, REPOS AND TOOLS WORTH USING →](https://www.aifordatabase.com/blog/metricflow/)
*   [UNION VS UNION ALL IN SQL: DUPLICATES, SPEED, PITFALLS →](https://www.aifordatabase.com/blog/union-vs-union-all-sql/)
*   [POSTGRES LIST ALL DATABASES: PSQL, SQL AND GUI METHODS →](https://www.aifordatabase.com/blog/postgres-list-all-databases/)

Source: https://www.aifordatabase.com/blog/postgres-boolean/