PostgreSQL Setup, psql & the Course Dataset — SQL Course

What you'll learn

Install or connect to PostgreSQL, navigate psql, load the course dataset, and build a repeatable query workspace. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Server vs client to a realistic data question
Apply Databases and schemas to a realistic data question
Apply psql commands to a realistic data question
Apply SQL files to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Server	The PostgreSQL process managing databases and sessions	Confirm host, port, database, and role before running SQL
psql meta-command	A client command beginning with backslash	Use it for navigation; keep portable data logic in SQL files
Seed script	A repeatable script creating known learning data	Make setup idempotent or rebuild from a clean database

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the repeatable PostgreSQL workspace question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Load and verify the course data

Stop on the first error, load one reviewed script, then verify the expected tables and counts.

setup.psql

\set ON_ERROR_STOP on
\i sql/course_dataset.sql
\dt course.*
SELECT 'authors' AS table_name, count(*) FROM course.authors
UNION ALL
SELECT 'books', count(*) FROM course.books;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Running an unknown script against the wrong database can change real data. Inspect the script and connection context before execution.

Independent workshop

Build a review-ready repeatable PostgreSQL workspace lab against the course commerce dataset.

Your finished workshop must include:

Server vs client
Databases and schemas
psql commands
SQL files
Course dataset
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Server: Confirm host, port, database, and role before running SQL
psql meta-command: Use it for navigation; keep portable data logic in SQL files
Seed script: Make setup idempotent or rebuild from a clean database

Quick check

1. Which rule best applies to Server?

2. Which rule best applies to psql meta-command?

3. Which rule best applies to Seed script?

Next: Tables, Rows, Columns & Data Types