What are common SQL data types and how do you choose them?

Question

Accepted Answer

Each column has a **data type** defining what it stores — numbers, text, dates, booleans, etc. Choosing the right type matters for **storage efficiency, correctness, and performance**. Types vary somewhat by database, but the categories are standard.

## Numeric types

```sql
INT / INTEGER          -- whole numbers (4 bytes)
BIGINT                 -- large whole numbers (8 bytes) — for IDs that grow large
SMALLINT               -- small whole numbers (save space)
DECIMAL(10,2) / NUMERIC -- EXACT decimal — use for MONEY (no rounding errors)
FLOAT / REAL / DOUBLE  -- approximate floating point — NOT for money
SERIAL / AUTO_INCREMENT -- auto-incrementing integer (for primary keys)
```

**Important:** use **`DECIMAL`/`NUMERIC` for money** (exact arithmetic), never `FLOAT`/`DOUBLE` (which have rounding errors that corrupt financial calculations).

## Text types

```sql
VARCHAR(n)    -- variable-length text up to n chars (the common choice)
CHAR(n)       -- FIXED-length (padded) — for codes of known length (e.g. country codes)
TEXT          -- large/unlimited text (articles, descriptions)
```

`VARCHAR(n)` is the everyday text type; `TEXT` for large content; `CHAR(n)` for fixed-length codes.

## Date/time, boolean, and others

```sql
DATE           -- date only (2024-01-15)
TIME           -- time only
TIMESTAMP      -- date + time (with or WITHOUT time zone — TIMESTAMPTZ in Postgres)
BOOLEAN        -- true/false
UUID           -- universally unique identifier (good for distributed IDs)
JSON / JSONB   -- structured JSON data (JSONB in Postgres is indexed/efficient)
BLOB / BYTEA   -- binary data
```

## Why choosing the right type matters

```text
✓ Storage — smaller types use less space (INT vs BIGINT; VARCHAR vs TEXT)
✓ Correctness — DECIMAL for money (exact), proper date types for dates, BOOLEAN for flags
✓ Performance — correct types index and compare efficiently; right size = less I/O
✓ Validation — the type itself constrains what can be stored (an INT can't hold text)
✓ Time zones — use TIMESTAMPTZ for timestamps to avoid time-zone bugs
```

## Why it matters

Choosing appropriate data types is a fundamental aspect of database design that affects **storage, correctness, and performance**, so understanding the common types and how to choose them is important everyday knowledge.

Knowing the categories — numeric (INT, BIGINT, DECIMAL, FLOAT), text (VARCHAR, CHAR, TEXT), date/time (DATE, TIMESTAMP), boolean, and special types (UUID, JSON/JSONB) — is necessary for designing tables correctly.

Several choices have real consequences worth understanding: using **`DECIMAL`/`NUMERIC` for money** rather than `FLOAT` (a critical correctness issue — floating-point rounding errors corrupt financial calculations, a classic, serious bug), choosing the right **integer size** (`BIGINT` for IDs that may grow beyond INT's range — a real concern for large tables), using proper **date/time types** (especially timezone-aware `TIMESTAMPTZ` to avoid time-zone bugs), and selecting appropriately-sized text types (VARCHAR vs TEXT).

The right type choice affects storage efficiency (smaller types use less space and I/O), correctness (the type validates and exactly represents data), and performance (proper types index and compare efficiently).

Since every column requires a type choice, and since wrong choices cause real problems (FLOAT for money corrupting calculations, INT overflow for IDs, timezone bugs), understanding common SQL data types and the principles for choosing them — exactness for money, appropriate sizing, timezone-aware timestamps — is important, foundational knowledge for sound database design that prevents a class of subtle but serious data correctness and performance issues.