SQL Reference ยท Dialects and Concepts
SQL booleans across dialects
How SQLite, PostgreSQL, MySQL, SQL Server and Oracle store true and false: BOOLEAN, BIT, TINYINT, 0 and 1, TRUE and FALSE literals, and NULL as unknown.
The SQL standard has a BOOLEAN type with three values: TRUE, FALSE and UNKNOWN (which is represented by NULL). Support is uneven. PostgreSQL has a real BOOLEAN. MySQL accepts the name but stores a TINYINT(1). SQL Server has no boolean column type and uses BIT. SQLite stores truth values as the integers 1 and 0. Oracle SQL had no boolean column type until 23ai.
Every system still has boolean expressions, such as price > 10, but they differ in whether such an expression is a value you can select or store.
SELECT price > 100 AS is_expensive FROM products LIMIT 3;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- SQLite flag INTEGER NOT NULL DEFAULT 0 CHECK (flag IN (0, 1)) WHERE flag = 1 -- TRUE and FALSE are aliases for 1 and 0 (3.23+) -- PostgreSQL flag BOOLEAN NOT NULL DEFAULT FALSE WHERE flag -- also: WHERE NOT flag, WHERE flag IS TRUE -- MySQL flag BOOLEAN NOT NULL DEFAULT FALSE -- stored as TINYINT(1) -- SQL Server flag BIT NOT NULL DEFAULT 0 WHERE flag = 1 -- Oracle (before 23ai) flag NUMBER(1) DEFAULT 0 NOT NULL CHECK (flag IN (0, 1))
Dialect differences
| Topic | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Column type | INTEGER (0 or 1) |
BOOLEAN |
BOOLEAN = TINYINT(1) |
BIT |
NUMBER(1); BOOLEAN in 23ai |
| Literals | TRUE, FALSE (3.23+), 1, 0 |
TRUE, FALSE, 't', 'yes', '1' |
TRUE, FALSE, 1, 0 |
1, 0, 'true' in conversions |
1, 0; TRUE in 23ai |
WHERE flag |
works | works | works (any non-zero is true) | error: needs = 1 |
error: needs = 1 |
SELECT a > b |
allowed | allowed | allowed | not allowed; use CASE |
not allowed; use CASE |
| Other values allowed | any, unless CHECK |
none | any integer (2 is stored) |
only 0, 1, NULL | any number, unless CHECK |
-- SQL Server and Oracle (before 23ai): a comparison is a condition, not a value SELECT name, CASE WHEN price > 100 THEN 1 ELSE 0 END AS is_expensive FROM products; -- PostgreSQL: three-valued tests SELECT name FROM users WHERE verified IS NOT TRUE; -- false and NULL SELECT name FROM users WHERE verified IS UNKNOWN; -- only NULL
Usage notes
- NULL is the third value.
WHERE flagkeeps only true rows, andWHERE NOT flagkeeps only false rows, so rows withNULLappear in neither. Declare flagsNOT NULLwith a default whenever you can. - Constrain the values. In SQLite, MySQL and Oracle, add
CHECK (flag IN (0, 1))so a stray2or'yes'cannot get in. IS TRUE,IS FALSE,IS UNKNOWNare standard and work in PostgreSQL and MySQL; SQLite hasIS 1/IS NOT 1instead (andIS TRUEsince 3.23).- Use
CASEto be portable.CASE WHEN condition THEN 1 ELSE 0 ENDproduces a 0/1 value in every dialect, including when you mustSUMthe matches to count them. - Drivers convert differently. The same column may arrive in your application as
true,1or"t". Check how your driver mapsBITandTINYINT(1). - Prefer a status column with named values (
'pending','active') over several boolean flags when the states are mutually exclusive.