BOOLEAN for true/false

Concepts covered: Basic Types

BOOLEAN holds exactly 2 values, TRUE and FALSE, written as bare keywords without quotes. The type answers a yes/no question about the row: is this subscription active, has this invoice been paid, did the user click the ad. A boolean occupies 1 byte, and the database compares, filters and indexes it without any conversion. Where a system has no native BOOLEAN, SQLite among them, TRUE and FALSE are stored and displayed as 1 and 0, which is what the examples on this page print.

Filtering on a flag

A boolean column is already a condition, so WHERE is_active and WHERE is_active = TRUE return the same rows, and WHERE NOT is_active returns the rest. The subscriptions table has 6 rows, 4 active and 2 cancelled. Run the query, then replace = TRUE with = FALSE, and then delete the comparison and put NOT in front of the column.

SELECT sub_id, customer, plan
FROM subscriptions
WHERE is_active = TRUE

Comparing a flag to a string, WHERE is_active = 'true', behaves differently on every engine. PostgreSQL accepts 'true' as a boolean literal and returns the active rows. MySQL stores BOOLEAN as a 1-byte integer, converts 'true' to 0, and silently returns the inactive rows instead. SQLite compares the stored 1 against the text 'true' and matches nothing. Writing the bare keyword TRUE, or the bare column, gives one answer everywhere. A flag stored as text costs more again: it accepts 'true', 'True', 'TRUE', 'yes' and 'Y' as 5 different values, so a filter written for one of them misses every row written with the others. On the BOOLEAN column there is nothing to misspell.

Comparisons produce booleans

A comparison is an expression whose result is a boolean. seats >= 10 evaluates to TRUE or FALSE for each row, the same way seats * 2 evaluates to a number, so it can be named in the SELECT list like any other column. The subscriptions table gains a seats column for this query; the 6 customers and their active flags are unchanged.

SELECT customer, seats, is_active,
  seats >= 10 AS is_large,
  is_active AND seats >= 10 AS active_large
FROM subscriptions

Omar's row is the one to read: 15 seats makes is_large 1, and the cancelled subscription makes active_large 0. A computed boolean can sit in the SELECT list as a derived flag, drive a WHERE clause, or be stored in a new column, and the same expression returns the same answer for each row in all 3 places.

Flags disguised as other types

• 4 GB

The integer form deserves attention because so much data arrives that way: the practice database stores in_stock, clicked and cache_hit as 0 and 1, which is how CSV exports and most analytics tools represent a flag. The integer version works until the day it does not: a 0/1 column accepts 2 and -1 without complaint, and a reader has to consult documentation to learn which direction means yes. BOOLEAN makes the 2 permitted values part of the schema, so a bad insert fails instead of becoming a third category in every report.

Naming carries the meaning. A column called active could hold a status string or a count of active days; is_active can only be a flag, and has_paid, can_edit and was_notified follow the same pattern. Name the column for the true case, so WHERE is_active reads as a sentence, and avoid negatives such as is_not_deleted, which forces WHERE NOT is_not_deleted on anyone looking for deleted rows.

A BOOLEAN column can also hold NULL, a third state meaning unknown, and the intermediate lesson covers how NULL combines with TRUE and FALSE in a filter.

A billing team is adding a column that records whether each invoice has been paid, and the choice of type decides how every later query on it reads.

Which type should the paid flag on an invoices table use?

• VARCHAR(5) holding 'true' or 'false'
• INTEGER holding 0 or 1
• BOOLEAN, named is_paid
• CHAR(1) holding 'Y' or 'N'

About this lesson

This section is part of the Data Types: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.