SQL Data Types

Introduction

Choosing the right data type for each column controls correctness, storage cost and performance. Get this wrong and you'll fight bugs for years.

Beginner analogy: data types are containers — you wouldn't store soup in a paper bag. Numbers go in numeric columns, dates in date columns, text in text columns.

Understanding the topic

Core concepts to understand:

• Numeric: INT, BIGINT, NUMERIC(10,2) for money, REAL/DOUBLE for science.
• Text: TEXT (Postgres prefers it), VARCHAR(n) (MySQL), CHAR(n).
• Dates: DATE, TIME, TIMESTAMP, TIMESTAMPTZ (with timezone).
• Boolean: BOOLEAN (true/false).
• JSON: JSONB (Postgres) — schemaless data inside a column.
• UUID — globally unique identifiers, common for distributed apps.

Syntax reference

Visual workflow / architecture:

Column Type → Storage / Use
─────────────────────────────────
INT          4 bytes   counts
BIGINT       8 bytes   IDs at scale
NUMERIC(p,s) variable  money
TEXT         variable  any string
TIMESTAMPTZ  8 bytes   events with TZ
BOOLEAN      1 byte    flags
JSONB        variable  flexible docs
UUID         16 bytes  distributed IDs

Real-world use

Stripe stores money as NUMERIC (never float). Every Postgres-backed SaaS uses TIMESTAMPTZ to avoid timezone bugs. JSONB powers half of modern Postgres apps for semi-structured data.

Best practices

• Money → NUMERIC, never FLOAT.
• Times → TIMESTAMPTZ, store UTC.
• Use UUIDs for distributed/multi-region systems.

Common mistakes

• Storing money in FLOAT/DOUBLE — leads to rounding errors.
• TIMESTAMP without timezone — silent bugs across regions.
• Overusing TEXT for everything (fine in Postgres, wasteful in MySQL).

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why never store money in FLOAT?
• Q2. Difference between TIMESTAMP and TIMESTAMPTZ.
• Q3. When to use UUID vs SERIAL/BIGINT?
• Q4. What is JSONB and when would you use it?
• Q5. VARCHAR vs TEXT.