1. Types

The Hook

A schema review at code-review time:

CREATE TABLE events (
  id          VARCHAR(50)  PRIMARY KEY,
  created_at  VARCHAR(20),
  amount      FLOAT,
  data        TEXT
);

Four columns, four wrong types. id as VARCHAR means PK lookups can't use compact integer indexes. created_at as VARCHAR means date arithmetic requires casting on every read. amount as FLOAT means binary-float drift on financial sums. data as TEXT containing JSON means no JSON indexing, no querying inside the JSON, just opaque blobs.

Every one of those decisions is permanent — changing a column type after data lands costs a migration with downtime risk. Schema-design choices are made on day 1 and live forever.

This chapter is the type-selection guide: which type for which kind of column, and what each choice locks you into. By the end you'll be able to design a schema that's correct, fast, and survives the data growing beyond your prototype expectations.

Table of contents

1. Numeric types
2. Character types
3. Temporal types
4. Boolean
5. JSON / JSONB
6. Arrays (Postgres)
7. UUID
8. Type selection rules
9. Edge cases and pitfalls
10. Production reality
11. Practice ladder
12. Cross-links
13. Final takeaway

Numeric types

Recap from Data Definition:

The decisions:

1. INT vs BIGINT for IDs. INT overflows at 2.1 billion. If you're certain your table will stay below that, INT saves 4 bytes per row (which compounds across indexes). For an "events" table that grows by millions per day, use BIGINT. For a "countries" table with 200 rows, INT is fine.
2. NUMERIC vs DOUBLE PRECISION. Money goes in NUMERIC. Physics measurements in DOUBLE PRECISION. If you're not sure, NUMERIC(p, s) is the safer default — it's slower but exact. The chapter on Numbers has the full discussion.
3. NUMERIC(p, s) choice. NUMERIC(12, 2) is fine for application-scale money (12 total digits, 2 after decimal — up to ±$10 billion with cent precision). Crypto and scientific applications use higher precision.

Character types

Default to TEXT in Postgres. No performance difference vs VARCHAR(n), no arbitrary length cap to revisit later.

VARCHAR(n) has one legitimate use: when you want the database to enforce a maximum length as a constraint. For an externally-provided field where exceeding the length is a bug, VARCHAR(n) is the schema enforcing it.

CHAR(n) is rarely useful. Padding to length surprises. Avoid.

Temporal types

Recap from Dates and Times:

Default to TIMESTAMPTZ for any "when did this happen" column. Storing UTC and converting on display is the correct approach. TIMESTAMP (without TZ) is a footgun for cross-timezone applications.

DATE is fine for fields that genuinely don't care about time-of-day — birthdates, holidays, due dates.

For epoch-millisecond timestamps (codefolio's hello_events.timestamp_ms), use BIGINT. Portable across SQL/NoSQL/JS, no timezone ambiguity at storage.

Boolean

is_active BOOLEAN NOT NULL DEFAULT TRUE

Postgres's first-class BOOLEAN accepts TRUE, FALSE, NULL. Use it.

JSON / JSONB

Postgres has two JSON types:

• JSON — stored as text. Validated for syntax. Queries re-parse on every access.
• JSONB — stored binary. Decomposed into a queryable form. Indexable. Slightly slower writes (decomposing), much faster reads.

Use JSONB in production unless you have a specific reason not to. The full treatment is in JSON in SQL (Advanced Patterns).

CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  payload JSONB NOT NULL
);
CREATE INDEX events_payload_user_idx ON events ((payload->>'user_id'));

That expression index makes WHERE payload->>'user_id' = '42' fast.

When to use JSON:

• Genuinely schemaless data — webhooks, audit logs of arbitrary changes, configuration blobs.
• Nested data where modelling each field as a column would create dozens of mostly-NULL columns.

When not to:

• Structured data with a known schema. Every field that has a known meaning belongs in its own column. JSON is a fallback, not a default.

Arrays

Postgres supports array column types: INTEGER[], TEXT[], etc.

CREATE TABLE posts (
  id BIGINT PRIMARY KEY,
  tags TEXT[]
);

When useful:

• Short, bounded lists (tags on a post, recipient list on an email).
• Querying with array operators (tags @> ARRAY['urgent'] for "contains").

When not:

• Long lists or lists that grow unboundedly. Use a child table.
• Lists you'll JOIN against. Arrays are awkward to join.

The general rule: prefer a child table for relational data; arrays for opaque short lists.

UUID

Globally unique 128-bit identifier. Useful when:

• ID generation can't be centralised (multi-region, offline-capable client).
• You don't want sequential IDs to leak business information ("user 12 signed up after user 11").
• Distributed systems where coordinator-free unique IDs simplify architecture.

Trade-offs:

• 16 bytes per row vs 4 (INT) or 8 (BIGINT). Larger indexes.
• Random UUIDs (v4) make B-tree inserts random — write performance suffers on large tables.
• UUID v7 (time-ordered) avoids the random-insert problem; modern code prefers v7.

Postgres has the uuid type and the pgcrypto extension's gen_random_uuid().

Type selection rules

Edge cases and pitfalls

Storing IDs as strings

Don't. Even if the ID has letters (e.g., 'INV-12345'), break it into components: kind TEXT NOT NULL, seq BIGINT NOT NULL. String IDs make joins and indexes slower; component IDs are flexible.

Choosing too narrow a type

Migrating from INT to BIGINT is much worse than starting with BIGINT. The conversion rewrites every row. Default to BIGINT for any column that might grow.

Storing timestamps as INT Unix seconds

Year 2038 problem. Use BIGINT (milliseconds) or TIMESTAMPTZ.

Implicit casts

'42' = 42 works in some dialects but not others. Type columns correctly so this never matters.

Production reality

The codefolio schema (from CLAUDE.md):

• visits.id: INT GENERATED ALWAYS AS IDENTITY — single-row counter table, will never exceed 2B IDs.
• visits.count: BIGINT NOT NULL DEFAULT 0 — the counter itself, unbounded.
• hello_events.id: BIGINT — append-only events, will exceed INT range.
• hello_events.timestamp_ms: BIGINT — epoch milliseconds; portable across systems.

Each choice reflects the type-selection rules above. The schema was designed before the system grew; the types accommodate growth without migration.

Practice ladder

1. What type for a price column? Hint: NUMERIC.
2. What type for a boolean is_paid flag? Hint: BOOLEAN.
3. What type for an event timestamp? Hint: TIMESTAMPTZ (or BIGINT ms-since-epoch for portability).
4. What type for a 1-million-rows table's primary key? Hint: INT is fine; if growth is uncertain, BIGINT.
5. A schema has email VARCHAR(255). Should you change to TEXT? Hint: in Postgres, no performance difference; either is fine; pick based on whether the cap is meaningful.
6. A column will store free-form JSON payloads. JSON or JSONB? Hint: JSONB for production.

Cross-links

• Previous module: CTEs and Recursion.
• Next in this module: Keys and References.
• Cross-reference: Numbers for the NUMERIC vs FLOAT decision; Dates and Times for the TIMESTAMP vs TIMESTAMPTZ decision.

Final Takeaway

Your Turn

Before you move on, check your understanding with the coach — explain the idea, apply it, weigh the trade-offs, then defend your reasoning.