Boolean & Binary Types

Alongside numbers, text, and dates, most databases give you a dedicated type for true/false flags and a family of types for storing raw binary data. Both are used less often than numeric or character columns, but understanding them — and their quirks — saves real confusion later.

BOOLEAN

A BOOLEAN column holds one of three possible values: TRUE, FALSE, or NULL. That third option is easy to forget — a nullable boolean isn't really a two-valued flag, it's a three-valued one, and NULL means "unknown," not "false." This matters a lot once the column is used inside a WHERE clause, because SQL's three-valued logic treats comparisons against NULL specially (covered in depth on the NULL pages).

BOOLEAN with three possible states

SQL

CREATE TABLE tasks (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    is_complete BOOLEAN NOT NULL DEFAULT FALSE, -- NOT NULL avoids the 3rd state
    is_urgent   BOOLEAN                          -- nullable: TRUE, FALSE, or unknown
);

SELECT * FROM tasks WHERE is_complete = FALSE;
SELECT * FROM tasks WHERE is_urgent IS NULL; -- "urgency not yet decided"

MySQL has no true BOOLEAN

Historically, MySQL does not have a real `BOOLEAN` type. `BOOLEAN` (and `BOOL`) are accepted as syntax but are silently mapped to `TINYINT(1)`, a 1-byte integer, where `0` means false and any nonzero value is treated as true. This works fine for typical use but means MySQL will happily let you insert `2` into what looks like a boolean column, and some client libraries display MySQL booleans as `0`/`1` rather than `true`/`false`. PostgreSQL and SQL Server (`BIT` in SQL Server's case) enforce a real boolean domain.

Binary types

Binary types store raw bytes rather than text — arbitrary data that isn't meant to be interpreted as characters in any particular encoding. Common names are BYTEA (PostgreSQL), BLOB (MySQL and many others), and VARBINARY (SQL Server, MySQL).

Type	Dialect	Typical use
BYTEA	PostgreSQL	Arbitrary binary data, small-to-medium size
BLOB	MySQL / general SQL	Binary Large OBject — images, files, serialized data
VARBINARY(n)	SQL Server / MySQL	Variable-length binary data up to n bytes

A binary column for a small hash or token

SQL

CREATE TABLE api_keys (
    id       SERIAL PRIMARY KEY,
    key_hash BYTEA NOT NULL   -- store a hashed token, not raw binary files
);

Should you store files in the database?

It is technically possible to store entire images, PDFs, or videos directly in a binary column, but doing so at any real scale usually causes problems: database backups balloon in size, replication and queries get slower, and the database becomes a bottleneck for what is fundamentally simple file storage.

Tip

In almost all real applications, store large files in a dedicated filesystem or object storage service (like S3-compatible storage), and store only a reference — a path or URL — in the database column. Reserve binary columns in the database itself for small pieces of binary data: hashes, tokens, small thumbnails, or serialized blobs that are always fetched together with the row.

Prefer BOOLEAN with NOT NULL DEFAULT FALSE for simple flags where "unknown" is not a meaningful state.
Allow NULL on a boolean only when "not yet decided" is genuinely different from both true and false.
Use binary types for small, always-fetched-together binary values, not general file storage.
Store a URL or path (as VARCHAR/TEXT) instead of the file itself for anything beyond a few kilobytes.