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.
CREATE TABLE features ( name TEXT, is_enabled BOOLEAN NOT NULL DEFAULT FALSE );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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. |
-- 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 FALSEfor flags soWHERE NOT flagdoes not silently skip rows where the flag isNULL. See NULL and three-valued logic. - Testing safely.
flag IS NOT TRUEmatches bothFALSEandNULL;flag = FALSEmatches onlyFALSE. - Add a CHECK in SQLite and MySQL, otherwise nothing stops
is_enabled = 7. - Comparing with
= TRUE. In SQLite,5 = TRUEis false becauseTRUEis exactly1, while5 IS TRUEis true because any nonzero value counts as true. PreferWHERE flagorWHERE flag <> 0. - Portable choice. If you need one schema that works everywhere, a
SMALLINTorCHAR(1)with aCHECKconstraint works in every database. - Aggregating flags.
SUM(is_enabled)counts the true rows when the values are0and1. On PostgreSQL writeCOUNT(*) 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.
Related
AND, OR and NOT, NULL and three-valued logic,IS NULL,CHECK,DEFAULT- Lessons: Data types, NULL