· intermediate
PostgreSQL
You already know SQL. This is what Postgres adds to it: types that refuse to lose your money, a cast operator you will use constantly, DISTINCT ON and FILTER for the queries that are awkward in standard SQL, and an upsert that is safe to run twice. Plus an honest account of which parts travel to other databases and which do not.
Written in house, from the work rather than from a syllabus. Every example is one we would use ourselves, and every exercise is checked against the same engine that grades your answer.
What you’ll learn
- Choose a Postgres type deliberately, and say why numeric beats double precision for money
- Cast with the double colon, and spot the casts that change the answer rather than the display
- Collapse a table to one row per group with DISTINCT ON
- Aggregate several subsets in one pass with FILTER rather than stacked CASE expressions
- Work with dates using date_trunc, EXTRACT and interval arithmetic
- Generate a complete date spine so a report shows the months with no data
- Write an upsert with ON CONFLICT that is safe to run twice
- Say what an index will and will not be used for, and read a plan far enough to tell
Before you start
- · SQL Foundations, and comfort with GROUP BY and joins
- · Intermediate SQL, or equivalent familiarity with subqueries and CASE