Menu

PostgreSQL BOOLEAN Data Type: TRUE, FALSE, NULL and Examples

Learn PostgreSQL BOOLEAN (BOOL) values, accepted true/false inputs, NULL handling, IS TRUE vs IS NOT TRUE, defaults, and runnable SQL examples.

PostgreSQL has a native BOOLEAN data type (also written BOOL). It represents true, false, or unknown (SQL NULL). A boolean value uses one byte of storage. Use it for flags such as is_active, is_verified, or enabled instead of storing arbitrary text.

BOOLEAN values and accepted input

In SQL, write the standard boolean constants TRUE, FALSE, and NULL.

PostgreSQL also accepts quoted text values when a boolean is expected or an explicit cast is used:

True values False values
'true', 't' 'false', 'f'
'yes', 'y' 'no', 'n'
'on' 'off'
'1' '0'

Input is case-insensitive and permits unambiguous prefixes. The textual output in PostgreSQL’s psql client is normally t for true and f for false.

SELECT
    'yes'::boolean AS from_yes,
    'off'::boolean AS from_off,
    '1'::boolean AS from_one;
from_yes | from_off | from_one
---------+----------+---------
t        | f        | t

Prefer TRUE and FALSE in new SQL code; the alternate forms are useful for importing existing data.

Create a BOOLEAN column with a default

CREATE TABLE feature_flags (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    enabled boolean NOT NULL DEFAULT FALSE
);

INSERT INTO feature_flags (name)
VALUES ('new-dashboard');

SELECT name, enabled
FROM feature_flags;
name          | enabled
--------------+--------
new-dashboard | f

DEFAULT FALSE is applied when enabled is omitted from an insert. NOT NULL prevents storing an unknown state. Omit NOT NULL when your application needs to distinguish not yet decided from false.

Filter true, false, and unknown correctly

WITH flags(id, enabled) AS (
    VALUES
        (1, TRUE),
        (2, FALSE),
        (3, NULL::boolean)
)
SELECT
    id,
    enabled,
    enabled IS TRUE AS is_true,
    enabled IS NOT TRUE AS is_not_true
FROM flags
ORDER BY id;
id | enabled | is_true | is_not_true
---+---------+---------+------------
1  | t       | t       | f
2  | f       | f       | t
3  | NULL    | f       | t

Notice that IS NOT TRUE matches both false and NULL. This is different from a simple negation of an unknown value.

The most useful filters are:

Predicate Matches
WHERE enabled or WHERE enabled IS TRUE True values only
WHERE enabled IS FALSE False values only
WHERE enabled IS NOT TRUE False and NULL
WHERE enabled IS NULL Unknown values only

WHERE NOT enabled does not match NULL rows. PostgreSQL uses three-valued logic, so NOT NULL evaluates to NULL, and the WHERE clause keeps only rows for which the condition is true.

Count boolean values including NULL

WITH flags(enabled) AS (
    VALUES (TRUE), (FALSE), (NULL::boolean), (TRUE)
)
SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE enabled IS TRUE) AS true_rows,
    count(*) FILTER (WHERE enabled IS FALSE) AS false_rows,
    count(*) FILTER (WHERE enabled IS NULL) AS unknown_rows
FROM flags;
total_rows | true_rows | false_rows | unknown_rows
-----------+-----------+------------+-------------
4          | 2         | 1          | 1

This works even when the column allows NULL. By contrast, count(enabled) counts only rows with a non-NULL boolean, regardless of whether its value is true or false.

Frequently asked questions

Is BOOLEAN the same as BOOL in PostgreSQL?

Yes. BOOL is an alias for the native BOOLEAN type.

Can BOOLEAN be NULL?

Yes, unless the column has a NOT NULL constraint. NULL represents an unknown value, not false.

Why does WHERE NOT enabled skip NULL rows?

SQL’s logical NOT preserves an unknown state. Use enabled IS NOT TRUE when you explicitly need both false and unknown rows.

Can I store 1 and 0 as boolean values?

PostgreSQL recognizes the quoted text inputs '1' and '0' when casting to boolean. For portable, readable SQL, prefer the unquoted constants TRUE and FALSE.

See the official PostgreSQL Boolean type documentation and Boolean comparison predicates for authoritative details.