CRUD, Filtering, Sorting, and Pagination

Master SELECT, INSERT, UPDATE, DELETE, predicates, sorting, limits, and safe pagination.

SELECT, WHERE, and Boolean Logic

SELECT chooses the columns and expressions to return. WHERE filters rows using predicates. Use AND, OR, and NOT deliberately, with parentheses when precedence might be unclear. IS NULL checks nullness; = NULL does not work as an ordinary equality test because NULL represents an unknown or missing value.

Avoid SELECT * in stable application queries when only a few columns are needed. Selecting explicit columns reduces data transfer and makes dependencies clearer, especially when schemas evolve.

Filter products

SELECT id, name, price
FROM products
WHERE in_stock = TRUE
  AND price >= 100
  AND name IS NOT NULL
ORDER BY price DESC;

💡 The query returns only the selected columns for in-stock products priced at least 100, ordered from highest to lowest price.

Key Takeaways

WHERE filters rows before final result presentation.
Use IS NULL and IS NOT NULL for null checks.
Explicit column lists make application queries clearer.

Knowledge Check

How should SQL test whether a column is null?

INSERT, UPDATE, and DELETE

INSERT adds rows, UPDATE changes matching rows, and DELETE removes matching rows. A missing or incorrect WHERE clause on an update or delete can affect every row. PostgreSQL's RETURNING clause can return changed rows without requiring a separate query, which is useful for generated IDs and API responses.

For multi-step operations, use transactions when the steps must succeed or fail together. In application code, use parameterized queries rather than concatenating user input into SQL strings.

Modify rows safely

INSERT INTO products (name, price)
VALUES ('Keyboard', 2499.00)
RETURNING id, name, price;

UPDATE products
SET price = 2299.00
WHERE name = 'Keyboard'
RETURNING id, price;

💡 RETURNING provides the inserted or updated values. In real application code, pass the product name and price as parameters rather than embedding dynamic input in the SQL text.

Key Takeaways

Review the WHERE clause before UPDATE or DELETE.
RETURNING can return affected rows and generated values.
Use parameterized queries for dynamic values.

Knowledge Check

Which PostgreSQL clause can return the rows affected by an INSERT or UPDATE?

Sorting, LIMIT/OFFSET, and Keyset Pagination

ORDER BY defines result ordering; without it, row order is not guaranteed. LIMIT caps the number of rows, and OFFSET skips rows. Offset pagination is easy to understand but can become slow for deep pages and may shift when concurrent inserts or deletes occur.

Keyset pagination uses a stable ordering key from the last item on the previous page. For example, when ordering by created_at DESC, id DESC, the next page can filter rows lexicographically before the last seen pair. A unique tie-breaker is important to avoid duplicates or omissions when sort values repeat.

Basic pagination

SELECT id, name, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

💡 This returns a page of 20 rows after skipping 40. For deep or frequently changing datasets, keyset pagination can be more efficient and stable.

Key Takeaways

Always define ORDER BY for deterministic pagination.
OFFSET can become costly for deep pages.
Keyset pagination relies on a stable sort and cursor values.

Knowledge Check

What is a key requirement for reliable pagination when sort values can repeat?

SELECT, WHERE, and Boolean Logic

INSERT, UPDATE, and DELETE

Sorting, LIMIT/OFFSET, and Keyset Pagination

STAY UPDATED WITH BYTES!