Choosing the right data type is the most consequential decision you make when designing a PostgreSQL schema. The type determines what values are valid, how much space a column uses, how it sorts and compares, and which operators and indexes apply. PostgreSQL's type system is unusually rich, so picking precisely the right type — rather than defaulting everything to text — pays off in correctness, storage, and performance.
Beyond the standard numeric, character, date, and boolean types, PostgreSQL offers types that often require add-ons elsewhere: native arrays, JSON and binary JSONB, UUIDs, ranges, network addresses, and full geometric types. Used well, these let the database enforce structure and answer questions that would otherwise be pushed into application code.
This lesson surveys the most important families and the trade-offs that guide selection: exact versus approximate numbers, the JSON-versus-JSONB choice, when an array is appropriate, and why UUIDs are common for keys. The goal is judgment — knowing not just that a type exists but when it is the right tool.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.