Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQL BOOLEAN type

Reference for the SQL BOOLEAN type: TRUE, FALSE and UNKNOWN, how SQLite stores booleans as 0 and 1, IS TRUE tests and what other databases use instead.

BOOLEAN is the SQL type for truth values. The standard defines three: TRUE, FALSE and UNKNOWN, where UNKNOWN is represented by NULL. Boolean columns are handy for flags such as is_active or is_published, and boolean expressions are what WHERE and CASE evaluate.

Support varies a lot. PostgreSQL has a true BOOLEAN type. MySQL accepts the name but stores a TINYINT(1). SQL Server and Oracle (before 23ai) have no boolean column type. SQLite has no boolean type either, but since version 3.23 it understands the keywords TRUE and FALSE as the integers 1 and 0.

SQL
CREATE TABLE features (
  name       TEXT,
  is_enabled BOOLEAN NOT NULL DEFAULT FALSE
);

Try it

SQL TRUE and FALSE in SQLite

Press Run (or Ctrl + Enter) to see the result here.

SQL A boolean flag column with a CHECK constraint

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
column_name BOOLEAN [NOT NULL] [DEFAULT TRUE | FALSE]

expression IS [NOT] TRUE
expression IS [NOT] FALSE
expression IS [NOT] UNKNOWN
Part Description
TRUE, FALSE Boolean literals. In SQLite they are the integers 1 and 0.
UNKNOWN The third truth value, which is NULL in practice. SQLite does not accept the UNKNOWN keyword; write IS NULL.
IS [NOT] TRUE Tests a value without ever returning NULL. NULL IS TRUE is false and NULL IS NOT TRUE is true.

Dialect differences

Database Boolean column Literals Notes
SQLite None (BOOLEAN gets numeric affinity) TRUE, FALSE (3.23+), 1, 0 Any integer is allowed unless you add a CHECK. Nonzero is true in conditions.
PostgreSQL BOOLEAN TRUE, FALSE, also 't', 'yes', '1' as strings Strict type. WHERE flag works directly.
MySQL BOOLEAN is an alias for TINYINT(1) TRUE = 1, FALSE = 0 Values such as 2 can be stored.
SQL Server BIT (0, 1 or NULL) 0, 1; strings 'true', 'false' convert Comparisons are not values: SELECT 1 = 1 is an error, use CASE.
Oracle BOOLEAN in SQL from 23ai; earlier versions use NUMBER(1) or CHAR(1) TRUE, FALSE in 23ai Older code stores 'Y'/'N' with a CHECK.
SQL
-- PostgreSQL
SELECT name FROM features WHERE is_enabled;
SELECT 'yes'::boolean;          -- true

-- SQL Server
SELECT name FROM features WHERE is_enabled = 1;
SELECT CASE WHEN 1 = 1 THEN 1 ELSE 0 END;

Usage notes

  • Three-valued logic. A nullable boolean has three states. Use NOT NULL DEFAULT FALSE for flags so WHERE NOT flag does not silently skip rows where the flag is NULL. See NULL and three-valued logic.
  • Testing safely. flag IS NOT TRUE matches both FALSE and NULL; flag = FALSE matches only FALSE.
  • Add a CHECK in SQLite and MySQL, otherwise nothing stops is_enabled = 7.
  • Comparing with = TRUE. In SQLite, 5 = TRUE is false because TRUE is exactly 1, while 5 IS TRUE is true because any nonzero value counts as true. Prefer WHERE flag or WHERE flag <> 0.
  • Portable choice. If you need one schema that works everywhere, a SMALLINT or CHAR(1) with a CHECK constraint works in every database.
  • Aggregating flags. SUM(is_enabled) counts the true rows when the values are 0 and 1. On PostgreSQL write COUNT(*) FILTER (WHERE flag) instead.
  • Indexing. A boolean has two values, so an ordinary index on it rarely helps; a partial index on the rare value can.