Chapter 8 — Key Takeaways

The big idea

PostgreSQL's built-in functions transform values right in the query — cleaning, formatting, and computing at the source rather than exporting to a spreadsheet. Know the categories so you know what to look up; keep this chapter (and Appendix C) as a reference.

Strings

  • || concatenates but propagates NULL; concat() ignores NULLs.
  • Core verbs: length, upper/lower/initcap, trim, substring, position, replace, split_part, left/right, lpad/rpad.
  • SQL strings are 1-indexed.

Numbers

  • round, ceil/floor, trunc, abs, mod/%, power/^, sqrt, random().
  • Integer ÷ integer = integer (7/2 = 3). Force decimals: 7.0/2, x::numeric/y, or 100.0*x/y. (Case Study 1 — the always-zero rate.)

Dates & times

  • now(), current_date, extract(field FROM ts), date_trunc('unit', ts), age(a,b), interval arithmetic (ts + interval '7 days'), to_char(ts, fmt).
  • Use half-open ranges for periods: >= start AND < next_start — not BETWEEN ... last_day (which drops the last day's times). (Case Study 2.)
  • timestamp ≠ timestamptz; be explicit with AT TIME ZONE for business calendars.

Type conversion

  • CAST(x AS t) (standard) ≡ x::t (PostgreSQL shorthand).
  • Formatted conversions: to_char, to_date, to_number.
  • Casting invalid text errors — clean/validate messy input first.

Conditional expressions

  • CASE WHEN … THEN … ELSE … END — first matching branch wins; works anywhere.
  • COALESCE(a, b, …) — first non-NULL (defaults, "zero not null").
  • NULLIF(x, 0) — safe division: a / NULLIF(b, 0) avoids divide-by-zero.
  • GREATEST/LEAST — per-row max/min across columns.
  • COUNT(*) FILTER (WHERE …) — clean conditional aggregates.

Performance (theme #5)

A function on a column in WHERE (e.g., lower(email) = …) can prevent an index from being used. Fix with an expression index or pre-normalized storage (Ch. 23).

You can now…

  • ☐ Format/parse text (handling NULL concatenation, 1-based indexing).
  • ☐ Compute numerically without the integer-division trap.
  • ☐ Slice, group, and format dates; use half-open ranges and time zones correctly.
  • ☐ Convert types with CAST/:: and to_char/to_date.
  • ☐ Use CASE/COALESCE/NULLIF/FILTER for logic and safe division.

Looking ahead

Chapter 9 — Subqueries. Queries nested in queries: filter by another query's result, compute correlated per-row values, and use EXISTS/IN — the first big payoff of closure (Ch. 4).

One sentence to carry forward: Most "mystery" SQL numbers trace back to two function-level traps — integer division and date-range boundaries — so force decimals and use half-open ranges by reflex.