Why this chapter matters
Most analysis starts here. Reliable filtering and sorting prevent incorrect snapshots and reduce rework in dashboards.
What you will learn
- Select specific columns and aliases instead of using SELECT *.
- Filter rows with precise boolean logic in WHERE clauses.
- Sort output with ORDER BY using multiple sort keys.
Understand the core ideas
PostgreSQL analysis starts with choosing the exact row grain you want to return, then writing a query that preserves that grain. If you are reporting one row per order, every selected column should describe that order row and every filter should narrow which orders appear. SELECT * hides intent and often pulls columns that create confusion later, so list only the fields needed for a decision and name computed columns with clear aliases. In PostgreSQL, the WHERE clause evaluates to true, false, or null, and null behaves differently from false in filters. That means conditions like shipped_at >= current_date - interval '30 days' skip null values unless you explicitly include them with OR shipped_at IS NULL. Good analysts treat this behavior as a feature, not a surprise, because reports often depend on whether missing timestamps represent incomplete work or truly inapplicable rows.
Sorting is also part of correctness, not just presentation. ORDER BY shipped_at DESC alone can produce unstable row order when many rows share the same timestamp, especially in batched imports. Add a deterministic tie break column such as order_id ASC so pagination, exports, and dashboard refreshes return the same sequence. Keep boolean logic explicit with parentheses when combining AND and OR, and test edge cases directly in SQL using small known subsets. In PostgreSQL, writing conditions in the same data type family prevents silent casts and protects index use, so compare numeric to numeric and timestamp to timestamp. A practical pattern is to draft a base query that returns the right row ids first, verify count and grain, then add display columns and sorting. This keeps early debugging focused on business logic instead of formatting details.
Key terms
- row grain
- The real world unit represented by one output row, such as one order or one customer day.
- predicate
- A boolean expression in
WHEREthat decides whether a row stays in the result. - alias
- A temporary output name for a column or expression, created with
AS. - deterministic sort
- An ordering that includes tie breakers so repeated runs return rows in the same sequence.
Shipped orders from last month with stable ordering
Use table orders(order_id, customer_id, total, status, shipped_at) at one row per order. Goal: list only shipped orders from the previous calendar month. Output grain stays one row per order.
- Define the time window in PostgreSQL terms: previous calendar month is
[date_trunc('month', current_date) - interval '1 month', date_trunc('month', current_date)), which avoids partial-month drift. - Filter only shipped records with
status = 'shipped'and require non-null ship timestamps through the range predicate onshipped_at. - Select only reporting columns (
order_id,customer_id,total,shipped_at) so the result is focused and easy to validate. - Apply
ORDER BY shipped_at DESC, order_id ASCto guarantee deterministic output when two orders share the same shipped timestamp.
SELECT order_id, customer_id, total, shipped_at FROM orders WHERE status = 'shipped' AND shipped_at >= date_trunc('month', current_date) - interval '1 month' AND shipped_at < date_trunc('month', current_date) ORDER BY shipped_at DESC, order_id ASC; returns exactly one row per shipped order in the target month, with stable ordering for refreshes and exports.A common misconception
Claim: ORDER BY is optional because SQL rows already have an insertion order.
Correction: PostgreSQL does not guarantee row order without ORDER BY. Physical storage order can change after vacuum, updates, or different plans, so explicit ordering is required for reliable reporting.
Lessons in this chapter
- Query shape and result setsRead a PostgreSQL SELECT statement top to bottom and predict output columns and rows. Read the full guide →
- Filtering with WHEREApply comparison and logical operators to encode business conditions directly in SQL.
- Sorting and tie-breakingUse ORDER BY with ascending or descending rules and deterministic tie-break columns.
- Null-safe reporting basicsHandle NULL values in predicates so record counts and filters stay accurate.
Study task
Chapter checkpoint
In PostgreSQL, how do you list customer_id and total from orders where total is at least 100, sorted by total descending?
SELECT customer_id, total FROM orders WHERE total >= 100 ORDER BY total DESC;