VisaSpark
Sign in
intermediate20 min

PostgreSQL Data Types and DDL

Choosing the right PostgreSQL column type for a value — including the types SQLite doesn't distinguish at all — and the DDL that defines a table.

What you'll learn

  • Choose an appropriate PostgreSQL data type for a given kind of value
  • Explain what SERIAL/IDENTITY, JSONB, and UUID are each for, and when to reach for them
  • Read a CREATE TABLE statement and identify its columns, types, and constraints

Prerequisites

Explanation

This lesson's code is genuine PostgreSQL syntax, shown for you to read and reason about — it is not executed by this sandbox, which runs SQLite, not PostgreSQL, and several of these types (JSONB, UUID, PostgreSQL's IDENTITY column behavior) either don't exist in SQLite or behave meaningfully differently there. You'll create and query real tables with these exact types in this module's guided local lab, on a real local PostgreSQL install.

PostgreSQL has a genuinely rich type system, considerably more specific than SQLite's famously permissive type affinity model. For numbers: INTEGER/BIGINT for whole numbers, NUMERIC(precision, scale) for exact decimal values where floating-point rounding would be unacceptable (money is the standard example — NUMERIC(10, 2) stores exactly two decimal places with no representation error), and REAL/DOUBLE PRECISION for approximate floating-point values where a tiny rounding error is acceptable. For text: TEXT for unbounded strings, VARCHAR(n) when a specific maximum length is a genuine business rule, not just a habit carried over from other databases. For time: TIMESTAMPTZ (timestamp with time zone) is almost always the right choice over plain TIMESTAMP for anything user-facing, since it stores an unambiguous instant rather than a wall-clock time whose meaning depends on an assumed, easily-lost time zone.

A surrogate primary key in modern PostgreSQL is typically declared id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY (the current, SQL-standard-aligned syntax — the older SERIAL pseudo-type still works and appears in plenty of existing code, but GENERATED ... AS IDENTITY is the more explicit, standard-conforming modern choice). UUID (gen_random_uuid(), built into PostgreSQL 13+) is an alternative surrogate key strategy — a randomly-generated, effectively-unique 128-bit value — useful specifically when IDs must be generated by the client before an insert (offline-first apps, distributed systems where coordinating a single sequential counter is impractical) or when you deliberately don't want IDs to reveal insertion order or row count to anyone who can see them. JSONB stores JSON data in an efficient, indexable, queryable binary format (as opposed to plain JSON, which stores an exact text copy with no such indexing) — useful for genuinely semi-structured data that doesn't fit a fixed column shape, though reaching for JSONB to avoid designing real columns for data that actually does have a fixed, known shape gives up exactly the constraint-enforcement and type-safety benefits normalization is meant to provide.

Example

Real PostgreSQL DDL, shown for reading only -- this sandbox runs SQLite, not PostgreSQL, and does not execute this. You'll run genuine statements like this yourself in this module's guided local lab.

CREATE TABLE learner (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    preferences JSONB NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE course (
    id             INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title          TEXT NOT NULL,
    price_usd      NUMERIC(10, 2) NOT NULL CHECK (price_usd >= 0),
    duration_hours REAL NOT NULL CHECK (duration_hours > 0)
);

-- A UUID primary key, generated by PostgreSQL itself:
CREATE TABLE session_token (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    learner_id INTEGER NOT NULL REFERENCES learner(id),
    expires_at TIMESTAMPTZ NOT NULL
);

Guided exercise

Guided exercise

Write choosePostgresType(kind) modeling type selection: 'money' -> 'NUMERIC(10,2)', 'wholeCount' -> 'INTEGER', 'unboundedText' -> 'TEXT', 'timestampt' -> 'TIMESTAMPTZ', 'semiStructured' -> 'JSONB'. Return null for any unrecognized kind.

Checks: money maps to an exact-decimal type, not a float · timestamps map to the timezone-aware type · unrecognized input returns null

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Write shouldUseUuid(reason) returning true only for the two genuine reasons a UUID primary key makes sense described in this lesson: reason === 'client-generates-id-before-insert' or reason === 'must-not-reveal-row-count'. Return false for any other reason (including a generic 'it seems more modern').

Checks: recognizes the offline/distributed-ID-generation case · recognizes the don't-reveal-row-count case · rejects an unjustified, non-technical reason

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Common mistakes

  • Using REAL or DOUBLE PRECISION for money -- floating-point types cannot represent most decimal fractions exactly, and rounding errors accumulate; NUMERIC(precision, scale) stores exact decimal values specifically to avoid this.
  • Using plain TIMESTAMP instead of TIMESTAMPTZ for user-facing times -- a plain TIMESTAMP has no time zone information, so its meaning depends on an assumed time zone that's easy to lose or misinterpret across services or users in different regions.
  • Reaching for JSONB to store data that actually has a fixed, well-known shape, just to avoid designing real columns -- this gives up constraint enforcement, type safety, and easy indexing on individual fields, for data that normalization's tools would have handled better.

Knowledge check

Knowledge check

1. Why is NUMERIC(10, 2) generally preferred over REAL/DOUBLE PRECISION for storing money?
2. What is the main practical advantage of TIMESTAMPTZ over plain TIMESTAMP for user-facing data?
3. This lesson's DDL examples are shown with language 'none' rather than 'sql'. Why?

Takeaway

Choose PostgreSQL types for what they actually guarantee — NUMERIC for exact decimals, TIMESTAMPTZ for unambiguous instants, JSONB only for genuinely semi-structured data — and recognize that this platform's SQLite sandbox can't honestly execute PostgreSQL-specific syntax, which is why this lesson's DDL is for reading, not running here.

Summary

PostgreSQL's type system is more specific than SQLite's: NUMERIC for exact decimals (money), TIMESTAMPTZ for unambiguous timestamps, GENERATED ALWAYS AS IDENTITY or UUID for surrogate keys, and JSONB for genuinely semi-structured data. This lesson's PostgreSQL-specific DDL is shown as static reference text, not executed by the SQLite-backed browser sandbox.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.

Next: Implementing a Normalized Schema in PostgreSQL →