Chapter 8 — Further Reading

This chapter is a reference; the docs below are the complete reference. Bookmark them.

Official reference (everyone)

  • PostgreSQL Docs: "Functions and Operators." The master catalog — string, numeric, date/time, formatting, conditional, JSON, and more. You will return here constantly. https://www.postgresql.org/docs/current/functions.html
  • "String Functions and Operators." Every string function, with examples.
  • "Data Type Formatting Functions" (to_char, to_date, to_number). The template patterns (YYYY, MM, FM, 999,990.00) you need for formatted I/O.
  • "Date/Time Functions and Operators." extract, date_trunc, age, intervals, AT TIME ZONE — the authoritative word on the least-portable part of SQL.

Dates done right (everyone — this bites everyone)

  • Articles on "half-open intervals" / "why BETWEEN is dangerous for dates." Reinforce Case Study 2; the half-open [start, end) pattern is a career-long habit worth forming.
  • "Storing and querying timestamps with time zones in PostgreSQL." A good explainer of timestamp vs timestamptz and AT TIME ZONE. Time-zone bugs are subtle and costly.
  • "Falsehoods programmers believe about time." A humbling, useful list. Read it once; respect dates forever.

Numbers & NULLs (💻 Developer · 📊 Analyst)

  • Any "integer division gotchas in SQL" post. Case Study 1's bug is ubiquitous; seeing it named makes you immune.
  • PostgreSQL Docs: "Conditional Expressions" (CASE, COALESCE, NULLIF, GREATEST/LEAST). Short and worth a full read.

Power features to grow into (🔬 CS Student · 💻 Developer)

  • FILTER (WHERE ...) for aggregates. Cleaner than SUM(CASE ...); standard SQL.
  • Regular-expression functions (regexp_replace, regexp_matches, ~). For text wrangling beyond LIKE — but remember full-text search (Ch. 16) for real search.
  • format() — printf-style string building, safer than hand-concatenation.

Reference cards (this book)

  • Appendix C — SQL Quick Reference: the function cheat-sheet.
  • Appendix D — PostgreSQL Data Types: what each type is and how it casts.
  • Appendix J — Dialect Differences: date/string functions across PostgreSQL, MySQL, SQL Server, Oracle, SQLite.

Do, don't just read

  • Reproduce both case-study bugs (integer division; BETWEEN dropping month-end), then fix them. Muscle memory beats memorization.
  • Build one "clean report" query that combines a string function, a date function, a CASE, and a COALESCE. That single query covers 80% of daily function use.

Next: Chapter 9 — Subqueries: queries inside queries.