PostgreSQL / Basic: relational foundations / Lesson 3

SELECT, NULL ordering and pagination

Turn a parcel question into a deterministic result. Control boolean logic, date boundaries, parameter binding and PostgreSQL’s default null placement.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Build precise predicates and stable ordering.
Explain PostgreSQL NULL placement and cursor boundaries.

Specify rows, columns and order

Projection chooses columns and WHERE chooses rows, but neither promises a display order. PostgreSQL may return a different order after an index, parallel plan or table-layout change. Write ORDER BY when order matters, and include a unique tie-breaker before LIMIT or cursor pagination. Avoid SELECT * for a stable application response because added columns change its shape and may expose unrelated data. Bind user values with the connector; identifiers and arbitrary sort fragments need an application allowlist.

State NULL placement explicitly

PostgreSQL treats NULL as sorting above non-NULL values for its default ordering: ascending normally puts NULL last and descending normally puts NULL first. This differs from assumptions learned in some other databases. The fixture explicitly requests descending fees with NULLS LAST, then uses parcel_id to break ties. Its expected identifiers are 2, 1 and 3. A NULL fee is unknown, not an expensive parcel; the written order communicates the intended report rather than relying on implicit defaults.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT parcel_id, fee FROM (VALUES
 (1, 20::numeric), (2, 30::numeric), (3, NULL::numeric)
) AS p(parcel_id, fee)
ORDER BY fee DESC NULLS LAST, parcel_id;

Expected output

parcel_id | fee
2         | 30
1         | 20
3         | NULL

Without NULLS LAST, the NULL row comes first for DESC in PostgreSQL.

Unknown logic changes filtering

WHERE keeps true predicates and discards false or unknown ones. fee = NULL therefore does not find missing fees. Use IS NULL, and use IS NOT DISTINCT FROM when equality should treat two NULLs as equal. AND binds more tightly than OR, so parentheses should state the business combination. NOT IN can become unknown when its comparison set contains NULL; a carefully correlated NOT EXISTS often expresses exclusion more directly for nullable data. Test both present and absent cases.

Use a half-open time interval

For a day of UTC instants, use an inclusive starting instant and exclusive next-day instant. Do not guess the last supported fractional second. The statement below is a pattern over a training shipments table, not a complete executable fixture. The cursor uses increasing shipment_id, so this particular report also orders by that identifier. If the order instead uses created_at and shipment_id, its cursor must compare the complete ordered tuple and define how nullable components are handled.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT shipment_id, created_at FROM quay_lab.shipments
WHERE created_at >= timestamptz '2026-10-01 00:00:00+00'
  AND created_at < timestamptz '2026-10-02 00:00:00+00'
  AND shipment_id > 100
ORDER BY shipment_id LIMIT 20;

Returned rows depend on the fixture. The explicit offsets avoid an accidental session-zone interpretation of day boundaries.

Pattern matching has its own contract

LIKE uses percent and underscore as pattern characters, so searching for a literal underscore requires deliberate escaping. PostgreSQL ILIKE provides case-insensitive pattern matching according to the applicable locale, but is not a general Unicode normalization policy. Text collation affects comparisons and ordering. Do not repair business identity issues by silently applying lower() to every column. First define matching semantics, then select a supported index or expression strategy. LIMIT bounds returned rows, not necessarily rows examined or authorization scope.

Common errors and fixes

Unknown fees appear first in descending reports

Why it happens: PostgreSQL DESC defaults to NULLS FIRST.
How to fix it: Specify the intended NULLS LAST or NULLS FIRST explicitly.
Prevent it next time: Include NULL values in sorting and pagination fixtures.

Rows repeat between pages

Why it happens: The order lacks a unique tie-breaker or the cursor omits a component.
How to fix it: Use a total order and a matching complete cursor predicate.
Prevent it next time: Test ties, boundary values and concurrent changes.

An exclusion query unexpectedly returns nothing

Why it happens: NULL in a NOT IN set made comparisons unknown.
How to fix it: Use an intentional NULL policy or correlated NOT EXISTS.
Prevent it next time: Review three-valued logic instead of assuming boolean-only comparisons.

Check your understanding

1. Fees are NULL, 10 and 20. What is PostgreSQL’s default ORDER BY fee DESC result, and how do you put unknown last?

Show worked solution

The default logical order is NULL, 20, 10. Write ORDER BY fee DESC NULLS LAST for 20, 10, NULL, then append a stable identifier if multiple fees tie. This controls presentation only; it does not change the meaning of NULL or make fee comparisons true for missing values.

2. A timestamp can be 23:59:59.750000. Repair a UTC day filter ending at <= 23:59:59.