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 | tPrefer 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 | fDEFAULT 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 | tNotice 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 | 1This 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.