Numeric, Character, Boolean, Date, Time, and Binary Types

Learning outcomes

A data type is not merely a storage size. It communicates a value’s domain, controls valid operations, influences comparisons and sorting, and helps the DBMS reject impossible data.

What a data type contributes

A type answers several questions: Which values are permitted? How are they represented? Which operators and functions apply? How are values compared and sorted? What happens when an input cannot be converted?

Numeric types: exact versus approximate

Integers and fixed-precision decimal types are exact within their supported range. Floating-point types are approximate binary representations designed for scientific measurements and wide dynamic ranges.

For a decimal declaration such as DECIMAL(12,2), the intent is up to twelve decimal digits in total, two of them after the decimal point. Exact limits and overflow behavior remain vendor-specific.

CREATE TABLE invoice_line (    invoice_line_id BIGINT PRIMARY KEY,    quantity INTEGER NOT NULL,    unit_price DECIMAL(12, 2) NOT NULL,    measured_mass DOUBLE PRECISION,    CHECK (quantity > 0),    CHECK (unit_price >= 0));

Character and text types

Character types hold textual data. Common declarations include CHAR(n), VARCHAR(n), and unbounded or large-text types such as TEXT. Their exact storage and length semantics vary by product.

• CHAR(n) describes fixed-length character data and may pad values in some systems.
• VARCHAR(n) describes variable-length text with a declared limit.
• TEXT commonly describes variable-length text without a small application-level limit.
• Unicode support depends on the DBMS, database encoding, and type family. SQL Server, for example, distinguishes VARCHAR and NVARCHAR.
• Collation controls comparison and ordering rules such as case, accents, and locale behavior.

Boolean types

A Boolean models two logical states: true and false. PostgreSQL has a native BOOLEAN type. Other systems use aliases or compact numeric types; SQLite stores Boolean-style values as integers, conventionally 1 and 0.

CREATE TABLE feature_flag (    flag_name TEXT PRIMARY KEY,    is_enabled INTEGER NOT NULL        CHECK (is_enabled IN (0, 1))) STRICT;INSERT INTO feature_flag VALUES ('new_catalogue', 1);-- This fails because 7 is not a valid flag value:INSERT INTO feature_flag VALUES ('unsafe_value', 7);

The check constraint turns a broad integer storage class into the intended two-value domain. This illustrates an important design principle: the declared type and constraints work together.

Date and time types

Temporal values require more decisions than “store a date.” Identify the business concept:

SQLite has no dedicated date/time storage class. It commonly stores ISO-8601 text, Julian day numbers, or Unix timestamps and supplies date/time functions to interpret them. Use one representation consistently and document its time-zone policy.

CREATE TABLE audit_event (    event_id INTEGER PRIMARY KEY,    event_name TEXT NOT NULL,    occurred_at TEXT NOT NULL        CHECK (datetime(occurred_at) IS NOT NULL)) STRICT;INSERT INTO audit_event (event_name, occurred_at)VALUES ('course_opened', '2026-08-05T10:30:00Z');SELECT event_name, occurred_at, datetime(occurred_at)FROM audit_event;

Binary types

Binary types store uninterpreted bytes. Typical uses include hashes, compact protocol payloads, encrypted values, signatures, and small file fragments. Names include BINARY, VARBINARY, BYTEA, RAW, and BLOB.

Do not choose a binary type merely because a value “looks technical.” A UUID, IP address, JSON document, or image might have a specialized native type, a validated textual representation, or an external object-storage location that better supports its operations.

CREATE TABLE checksum_sample (    sample_id INTEGER PRIMARY KEY,    algorithm TEXT NOT NULL,    digest BLOB NOT NULL) STRICT;INSERT INTO checksum_sample (algorithm, digest)VALUES ('demo', X'0A1B2C3D');SELECT algorithm, typeof(digest), length(digest), hex(digest)FROM checksum_sample;

SQLite storage classes and affinity

SQLite associates a storage class with each value: NULL, INTEGER, REAL, TEXT, or BLOB. Ordinary tables use type affinity and may convert inputs when possible. This flexibility differs from the stronger type enforcement many server databases apply.

STRICT tables support the core declarations INT, INTEGER, REAL, TEXT, BLOB, and ANY. They are excellent for this course because mistakes fail early while the environment remains lightweight.

Lab: inspect runtime types

DROP TABLE IF EXISTS type_lab;CREATE TABLE type_lab (    sample_id INTEGER PRIMARY KEY,    whole_count INTEGER NOT NULL,    measured_value REAL,    description TEXT,    raw_payload BLOB,    flexible_value ANY) STRICT;INSERT INTO type_lab    (whole_count, measured_value, description, raw_payload, flexible_value)VALUES    (12, 19.75, 'first sample', X'CAFE', '00123'),    (0, NULL, 'missing measurement', X'', 123);SELECT    sample_id,    typeof(whole_count) AS count_type,    typeof(measured_value) AS measure_type,    typeof(description) AS description_type,    typeof(raw_payload) AS payload_type,    typeof(flexible_value) AS flexible_typeFROM type_lab;-- Expected to fail in a STRICT table:INSERT INTO type_lab (whole_count) VALUES ('twelve');

Run the query and compare the declared type with typeof(), which reports the runtime storage class. Notice that the ANY column preserves the difference between text '00123' and integer 123.

Common mistakes

Choosing a type from sample data only

Ten sample rows cannot establish the full range, precision, or future operations. Use business rules and expected growth.

Storing dates in locale-specific text

Values such as 08/05/26 are ambiguous. Use a native temporal type or a documented, sortable representation such as ISO 8601.

Using text for every value

Text can store almost anything but weakens validation and makes numeric, temporal, and Boolean operations harder or unsafe.

Assuming the same type name behaves identically everywhere

Length semantics, implicit conversions, ranges, time zones, and aliases vary. Portability requires testing, not just matching names.

Checkpoint and practice

Summary and next lesson

Data-type families express more than storage: they preserve domains, enable valid operations, and support integrity. The next lesson turns these families into a repeatable selection process for real fields such as identifiers, money, measurements, dates, statuses, and flags.

References

• SQLite: Datatypes
• SQLite: STRICT Tables
• PostgreSQL: Data Types
• MySQL: Data Types
• SQL Server: Data Types