Why SQL data types matter

A SQL data type does three things at once: it constrains what values a column may hold, it controls how those values are stored on disk, and it determines which operators and functions can be applied. Choose well and the engine helps you — type-safe comparisons, index-friendly storage, fast arithmetic. Choose badly and you bake in slow scans, silent precision loss, and impossible-to-fix migration debt.

Two principles guide every type decision. First: pick the smallest type that will fit the value through the lifetime of the table — INT not BIGINT for a row counter that will never exceed two billion, VARCHAR(64) not VARCHAR(8000) for a fixed-length identifier. Second: use the most semantic type the engine offers — DATE not VARCHAR for dates, BOOLEAN not CHAR(1), JSONB not TEXT for JSON. Both rules give the planner more information and the storage engine fewer bytes.

Numeric types: integers, decimals, floats

Numeric types split into three families: exact integers (TINYINT through BIGINT), exact decimals (DECIMAL / NUMERIC), and approximate floats (REAL / DOUBLE PRECISION / FLOAT). Pick by the question "is this counted, measured, or money?".

• TINYINT (1 byte, 0–255 unsigned / -128–127 signed) — flags, small enums, status codes. Postgres has no TINYINT; use SMALLINT (2 bytes).
• SMALLINT (2 bytes, ±32K) — counters in fixed small ranges, day-of-year indices.
• INT / INTEGER (4 bytes, ±2.1B) — the default for surrogate primary keys on small-to-medium tables. Watch the 2.1B ceiling; row counts that look safe today have a habit of doubling.
• BIGINT (8 bytes, ±9.2 × 10^18) — primary keys on tables that will exceed 2 billion rows, monotonically growing event IDs, microsecond timestamps stored as integers.
• DECIMAL(p, s) / NUMERIC(p, s) — exact arithmetic with p total digits and s after the decimal. Money, percentages, anything where 0.1 + 0.2 must equal 0.3 exactly. ALWAYS for currency.
• REAL (4 bytes) / DOUBLE PRECISION (8 bytes) / FLOAT — IEEE-754 binary floats. Fast, range-flexible, NOT EXACT — 0.1 + 0.2 = 0.30000000000000004. Use for measurements (sensor readings, scientific data), never for money.

-- Money: 19 digits is enough for ~ $9.2 quadrillion at 2dp
amount DECIMAL(19, 2) NOT NULL,

-- Sensor reading: range matters more than precision
temperature_c REAL,

-- Surrogate PK on a high-volume table: BIGINT from day one
event_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

-- Boolean-like flag in MySQL where TINYINT is the historical convention
is_active TINYINT(1) NOT NULL DEFAULT 1,

Character types: VARCHAR, CHAR, TEXT, NVARCHAR

Character types vary more across dialects than any other family. Get the variable-vs-fixed-width distinction right, pick UTF-8 collations on day one, and keep length limits realistic.

• CHAR(n) — fixed length. The engine pads with spaces to exactly n characters on write and (depending on dialect) trims them on read. Use only for genuinely fixed-length values: ISO country codes (CHAR(2)), currency codes (CHAR(3)), fixed-format booking refs.
• VARCHAR(n) — variable length up to n characters. Storage is content-length + 1–4 bytes of length prefix. The default for almost every text column.
• TEXT / CLOB — variable length without (or with very large) limit. PostgreSQL: TEXT and VARCHAR have identical performance; choose TEXT for documents and unbounded user input. MySQL: TEXT is stored off-page and is slightly slower than VARCHAR for indexed columns.
• NVARCHAR / NCHAR — Unicode-explicit forms in SQL Server and Oracle. UTF-16 in SQL Server pre-2019, UTF-8 since 2019 with the right collation. Always pick the Unicode variant for user-facing text in SQL Server.
• Length is in characters in PostgreSQL, MySQL utf8mb4 and modern SQL Server; in bytes in Oracle by default (set NLS_LENGTH_SEMANTICS=CHAR to get character semantics).

VARCHAR vs CHAR: when to use which

• CHAR(n) is the right choice ONLY when every value really is exactly n characters. The classic legitimate uses: ISO 3166 country codes (CHAR(2)), ISO 4217 currency codes (CHAR(3)), fixed-format identifiers like flight numbers AA1234.
• VARCHAR is correct for everything that has a maximum length but variable actual length: names, emails, descriptions, identifiers, URLs.
• Performance: on PostgreSQL, CHAR is actually slightly slower than VARCHAR because of the trailing-space handling. On MySQL InnoDB, CHAR has a tiny seek-time advantage on disk but VARCHAR wins for any column that varies in length. The "CHAR is faster than VARCHAR" folklore is decades out of date.
• Trailing-space behaviour differs across dialects: CHAR(3) storing "A" returns "A " (with two trailing spaces) in some engines, "A" in others. Stick to VARCHAR to avoid the gotchas.

Date and time types: DATE, TIME, TIMESTAMP, TIMESTAMPTZ

Time-zone handling is where most teams pick the wrong type and pay for it for years. The rule is simple: if the value represents a moment in time (an event, a log entry, a transaction), use a timezone-aware type and store everything in UTC. If it represents a wall-clock time independent of any timezone (a recurring schedule, a birthday, a business holiday), use a timezone-naive type or a plain DATE.

• DATE (3–4 bytes) — calendar date with no time component. Birthdays, due dates, holidays.
• TIME (3–8 bytes) — wall-clock time with no date. Rarely useful on its own.
• TIMESTAMP / DATETIME — date + time, NO timezone. Stored as the literal value the user supplied. Almost never the right choice for application events.
• TIMESTAMPTZ (PostgreSQL) / TIMESTAMP WITH TIME ZONE (Oracle) / DATETIMEOFFSET (SQL Server) — date + time, normalised to UTC on write, returned in the session timezone on read. The correct type for "when did this happen".
• INTERVAL — duration ("2 hours 30 minutes"). PostgreSQL and Oracle native; emulate in MySQL/SQL Server with NUMERIC seconds.

TIMESTAMP vs DATETIME vs TIMESTAMPTZ across dialects

The same keywords mean different things in different engines. This single section will save you a production incident.

• PostgreSQL: TIMESTAMP = no timezone (8 bytes); TIMESTAMPTZ = stored in UTC, returned in session TZ (8 bytes). DATETIME does NOT exist.
• MySQL: TIMESTAMP (4 bytes, range 1970–2038, stored in UTC and converted) — beware the 2038 problem; DATETIME (8 bytes, range 1000–9999, no timezone, stored as supplied). For event data, prefer DATETIME for the wider range or TIMESTAMP for automatic UTC handling.
• SQL Server: DATETIME (legacy, 8 bytes, 3.33ms precision); DATETIME2 (6–8 bytes, 100ns precision, range 0001–9999) — always prefer DATETIME2 over DATETIME; DATETIMEOFFSET (10 bytes) for timezone-aware. SMALLDATETIME (4 bytes) for minute-precision use cases.
• Oracle: DATE (date + time, 7 bytes, second precision); TIMESTAMP (variable bytes, fractional seconds); TIMESTAMP WITH TIME ZONE / TIMESTAMP WITH LOCAL TIME ZONE for the two timezone semantics.
• SQLite: dynamically typed — store ISO-8601 strings or Unix epochs. SQLite has no native TIMESTAMP; it relies on convention.

-- The right shape of an event-log table on each engine
-- PostgreSQL
CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- MySQL 8 (DATETIME because TIMESTAMP's 2038 cliff is too close)
CREATE TABLE events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

-- SQL Server (DATETIMEOFFSET to keep the source timezone)
CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  occurred_at DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET()
);

Boolean and bit types

• PostgreSQL: BOOLEAN — true / false / NULL. The clean choice.
• MySQL: BOOLEAN is a synonym for TINYINT(1). Stores 0 or 1; comparisons against TRUE / FALSE work but the underlying type is integer. Ranged values (2, 3, …) silently store.
• SQL Server: BIT — 0, 1 or NULL. Stored efficiently (multiple BIT columns share a byte). No literal "true" — use 1.
• Oracle: no native boolean in SQL until 23c; convention is CHAR(1) NOT NULL CHECK (col IN ('Y', 'N')) or NUMBER(1). Oracle 23c adds BOOLEAN proper.
• SQLite: no boolean type; stores 0 / 1 in any numeric column.

Binary types: BYTEA, BLOB, VARBINARY

• PostgreSQL BYTEA, MySQL BLOB / MEDIUMBLOB / LONGBLOB, SQL Server VARBINARY(MAX), Oracle BLOB. All store raw byte sequences with sizes from KB to GB.
• Storage rule: small (< 1KB) goes inline; large (> 8KB usually) goes off-page or in TOAST tables (Postgres). Querying or aggregating across many large BLOBs is slow.
• For files larger than a few MB, prefer object storage (S3, GCS, Azure Blob) with a VARCHAR URL in the database. Only put binary in the database when you genuinely need transactional consistency with rows.

JSON types: JSON vs JSONB and dialect equivalents

• PostgreSQL JSON — stores the exact text the user supplied. Preserves whitespace, key order, duplicate keys. Slow to query (re-parsed on every access).
• PostgreSQL JSONB — binary-decomposed; faster querying, supports GIN indexes, drops whitespace and duplicate keys. Default choice for any JSON column you will query.
• MySQL JSON (5.7+) — binary-stored, indexable via generated columns. Equivalent in spirit to JSONB.
• SQL Server has no native JSON type — store as NVARCHAR(MAX) and use the JSON_VALUE / JSON_QUERY / OPENJSON functions. Performance is acceptable but indexing requires computed columns.
• Oracle 21c+ has a native JSON type; older versions use BLOB or VARCHAR with a CHECK constraint.

UUID, identity columns and surrogate keys

• UUID — 128 bits / 16 bytes. Native in PostgreSQL (UUID), SQL Server (UNIQUEIDENTIFIER) and Oracle (RAW(16)); MySQL stores as BINARY(16) or CHAR(36). Use UUIDv7 (timestamp-prefixed) for primary keys to keep B-tree inserts cheap.
• BIGINT IDENTITY / SEQUENCE — monotonically increasing 8-byte integers. Smaller index than UUID, faster inserts, but reveals row counts and creates contention on the last page in high-write workloads.
• Composite natural keys — (tenant_id, external_ref) etc. Cleaner semantically but every foreign key carries the full key. Almost always lose to a small surrogate plus UNIQUE constraint.
• Avoid INT IDENTITY (4 bytes, 2.1B ceiling) for any table that might ever grow. Migrating from INT to BIGINT on a hot table is one of the most painful operations in databases.

Arrays, enums and domain-specific types

• Arrays — first-class in PostgreSQL (any type can be made into an array: INT[], TEXT[]). Other engines emulate with JSON or normalise into a child table. Useful for read-heavy denormalisation; index with GIN.
• ENUMs — PostgreSQL ENUM (typed, fast equality, painful to alter), MySQL ENUM (per-table, similar tradeoffs). Prefer a small lookup table + foreign key for any enum that might gain values.
• Domain-specific: PostgreSQL has INET, CIDR, MACADDR, point/polygon (PostGIS), tsvector (full-text), TSRANGE (time ranges). When the engine offers a semantic type, use it — operators and indexes specialise.
• XML — supported in PostgreSQL, SQL Server and Oracle. Prefer JSON/JSONB unless you have an existing XML investment.

Casting, CAST vs CONVERT, and implicit conversion traps

-- ANSI-standard CAST: portable, recommended
SELECT CAST(price AS INTEGER) FROM products;
SELECT amount::DECIMAL(19, 2) FROM orders;  -- PostgreSQL shorthand

-- CONVERT: dialect-specific, formatting options
SELECT CONVERT(VARCHAR(10), order_date, 23) AS iso_date FROM orders; -- SQL Server
SELECT CONVERT(amount, DECIMAL(19, 2)) FROM orders;                  -- MySQL

• CAST is the SQL-standard form, supported everywhere with identical syntax. Use it by default.
• CONVERT is dialect-specific: SQL Server's CONVERT takes an optional format style for date strings; MySQL's CONVERT is essentially CAST with reordered arguments.
• PostgreSQL :: shorthand is convenient (`amount::INTEGER`) but non-portable. CAST in production code, :: in psql exploration.
• Implicit conversion happens silently when an expression mixes types. SQL Server's data-type precedence list is well-defined; PostgreSQL refuses many implicit conversions; MySQL is the most permissive (and therefore the most error-prone).
• The expensive trap: comparing an indexed column to a value of a different type can disable the index. `WHERE varchar_id = 12345` (int literal) forces a per-row CAST and a full table scan. Quote literals to match the column type: `WHERE varchar_id = '12345'`.

Storage size and performance: pick small types deliberately

• Every byte you save in a column saves one byte per row, every secondary index, every replica, every backup. On a 1B-row table, dropping a column from BIGINT to INT saves 4 GB of primary-storage data plus matching savings on every index.
• Fixed-width types (INT, BIGINT, DATE, TIMESTAMP) sort and compare faster than variable-width types (VARCHAR, JSON, BLOB). Primary keys and join columns should be fixed-width when you have the choice.
• Index size is dominated by the indexed column type. A UNIQUE INDEX on a UUID column is twice as large as the same index on a BIGINT — and B-tree fanout is half, which means deeper trees and more I/O per lookup.
• For VARCHAR, the maximum length parameter does NOT affect storage of shorter values, but it DOES affect how much memory the planner reserves for sort buffers and hash tables. VARCHAR(255) costs no more disk than VARCHAR(64) for "abc" but consumes 4× the working memory during sorts.

Dialect comparison: PostgreSQL vs MySQL vs SQL Server vs Oracle vs SQLite

Use this matrix when designing a schema that must run on more than one engine, or when porting one to another.

• Surrogate key — Postgres: BIGINT GENERATED ALWAYS AS IDENTITY; MySQL: BIGINT AUTO_INCREMENT; SQL Server: BIGINT IDENTITY; Oracle: NUMBER(19) GENERATED AS IDENTITY (12c+); SQLite: INTEGER PRIMARY KEY.
• Money — Postgres / MySQL / SQL Server: DECIMAL(19, 2); Oracle: NUMBER(19, 2); SQLite: NUMERIC.
• Variable text — Postgres: TEXT (unbounded) or VARCHAR(n); MySQL: VARCHAR(n) (≤ 65,535 bytes); SQL Server: NVARCHAR(n) or NVARCHAR(MAX); Oracle: VARCHAR2(n) (≤ 4000 by default, 32K with extended); SQLite: TEXT.
• Event timestamp — Postgres: TIMESTAMPTZ; MySQL: DATETIME(6) or TIMESTAMP (UTC, 2038 risk); SQL Server: DATETIMEOFFSET or DATETIME2; Oracle: TIMESTAMP WITH TIME ZONE; SQLite: TEXT (ISO-8601) or INTEGER (epoch).
• Boolean — Postgres: BOOLEAN; MySQL: TINYINT(1); SQL Server: BIT; Oracle: NUMBER(1) or BOOLEAN (23c+); SQLite: INTEGER.
• JSON — Postgres: JSONB; MySQL: JSON (5.7+); SQL Server: NVARCHAR(MAX) + JSON functions; Oracle: JSON (21c+) or BLOB; SQLite: TEXT (with json1 extension).
• UUID — Postgres: UUID; MySQL: BINARY(16) or CHAR(36); SQL Server: UNIQUEIDENTIFIER; Oracle: RAW(16); SQLite: TEXT or BLOB.

Practice: hands-on data-type lessons

Each topic above maps to a hands-on lesson in the Learning Mode curriculum. Lessons include runnable PostgreSQL examples, exercises against the HR, E-commerce and Banking schemas, and dialect-comparison drills.