Chapter 2 — Key Takeaways

The big idea

A database is a server you connect to, not a file you open. You installed PostgreSQL, met its client psql, created the mercado database, loaded the practice data, and ran your first queries. Setup friction is where most people quit — you got past it.

What you set up

  • PostgreSQL 15+, installed natively (macOS/Windows/Linux) or via Docker (the most reproducible option — one command, identical for everyone).
  • psql, the official command-line client — universal, scriptable, copy-pasteable.
  • Optionally a GUI (pgAdmin/DBeaver), which connects with the same host/port/database/user.

The client/server model

client (psql / GUI / your app)  ──SQL──►  server (owns the data)
                                ◄─rows──
  • The server is a background process that owns your data and listens on port 5432.
  • A client connects with four facts: host, port, database, user/password.
  • This split is why many clients share one database safely, why your Python app (Part V) connects the same way, and why production can run in the cloud without your code changing.

Essential psql survival kit

Command Use
\l list databases
\c mercado connect to a database
\dt list tables (in the current database)
\d customers describe a table's structure
\i file.sql run a SQL file
\x / \timing toggle expanded display / query timing
\conninfo where am I connected?
\q quit
  • End SQL statements with ;. A -# prompt means "still waiting" — you forgot the semicolon (Ctrl+C to cancel).

The practice data

  • schema.sql → 13 tables, keys, constraints (no data).
  • seed-sample.sql → small, deterministic data; book output matches your screen. Use this now.
  • generate_data.sql → ~100K-order dataset for the performance chapters (23–25). Load it later.
  • Verify with \dt (13 tables) and a few count(*) checks.

Hard-won debugging wisdom

  • Always know where you are (\conninfo): a specific server, database, and user. "My tables vanished" is almost always "I'm in the wrong database/server," not data loss.
  • The namespacing has three levels: server → database → schema (public).
  • Two servers (local + Docker) fighting over port 5432 cause nondeterministic connections — run one at a time or use different ports.

You can now…

  • ☐ Install and start PostgreSQL (including via Docker).
  • ☐ Connect with psql and navigate with meta-commands.
  • ☐ Create a database; create/insert/query/drop a table.
  • ☐ Load and verify the Mercado schema and sample data.
  • ☐ Diagnose the most common connection and "missing table" errors.

Looking ahead

Chapter 3 — The Relational Model. With a live database to explore, you'll learn the real vocabulary: relations (tables), tuples (rows), attributes (columns), domains, primary and foreign keys, the meaning of NULL, and the integrity constraints that keep Mercado honest. That vocabulary underlies every remaining chapter.

One sentence to carry forward: You don't open a database — you connect to one, and knowing exactly which server and database you're connected to is the first skill of every database professional.