Chapter 14 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "CREATE TABLE," "ALTER TABLE," "DROP TABLE." The complete grammar — constraints, referential actions, identity columns, partitioning options. https://www.postgresql.org/docs/current/sql-createtable.html
  • PostgreSQL Docs: "Data Definition" (the whole chapter). Columns, defaults, constraints, system columns, modifying tables, privileges, and schemas — the definitive tutorial-style treatment.
  • PostgreSQL Docs: "Constraints." Deep dive on CHECK, NOT NULL, UNIQUE, primary/foreign keys, and exclusion constraints.

Types (everyone)

  • PostgreSQL Docs: "Data Types." The full catalog; pair with Appendix D of this book for the practical subset.
  • "Don't store money in floats" / "numeric vs float for currency." Reinforces the money lesson (Chapters 3, 8, 14).
  • "text vs varchar in PostgreSQL." Why the length limit is usually unnecessary.

Safe schema changes (💻 Developer · 🏗️ DBA) — pairs with Chapter 22

  • "Zero-downtime migrations in PostgreSQL" / "Safe ALTER TABLE." The lock-aware techniques behind Case Study 2: nullable-then-backfill, NOT VALID + VALIDATE, CREATE INDEX CONCURRENTLY, lock_timeout. Essential reading before you ALTER anything in production.
  • PostgreSQL Docs: "Explicit Locking" (the lock-strength table). Which operations take ACCESS EXCLUSIVE and why it blocks everything.
  • Migration tools: Flyway, Liquibase, Alembic (Python/SQLAlchemy) — you'll use these in Chapter 22.

Identity & sequences (🔬 CS Student · 💻 Developer)

  • PostgreSQL Docs: "Identity Columns" and "CREATE SEQUENCE." GENERATED ALWAYS vs BY DEFAULT, and working with sequences directly.
  • "serial vs identity in PostgreSQL." Why IDENTITY is the modern, standard choice over serial.

Reference (this book)

  • Appendix D — PostgreSQL Data Types: the practical type reference.
  • Appendix C — SQL Quick Reference: DDL syntax.
  • Appendix J — Dialect Differences: types, identity, and DDL across databases.

Do, don't just read

  • Build your project's full schema (the progressive-project exercise) and try to violate every constraint — watch the database refuse. That's the contract working.
  • Practice the safe ALTER pattern: add a column nullable, backfill, set default, then SET NOT NULL.
  • Read Mercado's sql/schema.sql end to end now that you understand every line — it's a worked example of well-constrained DDL.

Next: Chapter 15 — Views, Materialized Views, and Functions: reusable SQL objects.