PostgreSQL Zero to Advanced #4: Filtering, Sorting & Pagination

In Part 3 we filled our course platform with 8 users, 8 courses and 14 orders. Real applications rarely need all rows, though. A search page needs matching courses, a dashboard needs last month's orders, and an API needs page 2 of 10. In this part you will learn to filter, sort and paginate precisely.

WHERE: comparison operators

WHERE keeps only rows for which the condition is true. The usual operators are available: =, <> (or !=), <, <=, >, >=.

SELECT id, title, price
FROM courses
WHERE price > 250000;

Output:

 id |             title             | price  
----+-------------------------------+--------
  2 | PostgreSQL Performance Tuning | 350000
  5 | Advanced SQL Queries          | 275000
  6 | Next.js Full Stack            | 400000
  7 | Python for Data Analysis      | 300000
(4 rows)

Combining conditions with AND / OR

AND requires both conditions to be true; OR requires at least one:

SELECT title, category, level
FROM courses
WHERE category = 'database'
  AND level <> 'beginner';

Output:

             title             | category |    level     
-------------------------------+----------+--------------
 PostgreSQL Performance Tuning | database | advanced
 Advanced SQL Queries          | database | intermediate
(2 rows)

SELECT title, price, is_published
FROM courses
WHERE price < 150000
   OR is_published = false;

Output:

              title               | price  | is_published 
----------------------------------+--------+--------------
 JavaScript Basics                | 120000 | t
 Docker Essentials for Developers | 200000 | f
(2 rows)

When you mix AND and OR, AND is evaluated first. Use parentheses to make your intent explicit: WHERE category = 'web' AND (price < 150000 OR level = 'advanced').

Handling NULL correctly

NULL means "unknown". Comparing anything to unknown gives unknown, not true, so this query finds nothing even though Agus has no city:

SELECT name FROM users WHERE city = NULL;

Output:

 name 
------
(0 rows)

Always use IS NULL or IS NOT NULL:

SELECT name, city FROM users WHERE city IS NULL;

Output:

    name    | city 
------------+------
 Agus Salim | 
(1 row)

Pattern matching: LIKE and ILIKE

LIKE matches text against a pattern. % means "any number of characters" and _ means "exactly one character".

SELECT title FROM courses WHERE title LIKE '%SQL%';

Output:

             title             
-------------------------------
 SQL Fundamentals
 PostgreSQL Performance Tuning
 Advanced SQL Queries
(3 rows)

LIKE is case-sensitive, so a lowercase pattern finds nothing:

SELECT title FROM courses WHERE title LIKE '%sql%';

Output:

 title 
-------
(0 rows)

PostgreSQL's ILIKE is the case-insensitive version, ideal for simple search boxes:

SELECT title FROM courses WHERE title ILIKE '%sql%';

Output:

             title             
-------------------------------
 SQL Fundamentals
 PostgreSQL Performance Tuning
 Advanced SQL Queries
(3 rows)

The underscore wildcard is useful for fixed positions. Here, emails whose second letter is u:

SELECT name, email FROM users WHERE email LIKE '_u%';

Output:

     name     |      email       
--------------+------------------
 Budi Santoso | budi@example.com
 Rudi Hartono | rudi@example.com
(2 rows)

A pattern that starts with % cannot use a normal index, so it scans the whole table. That is fine for small tables; for real search features use full-text search, which we cover in Part 10.

IN and BETWEEN

IN is a clean replacement for a chain of OR conditions on the same column:

SELECT name, city
FROM users
WHERE city IN ('Bandung', 'Surabaya', 'Medan');

Output:

     name     |   city   
--------------+----------
 Siti Rahma   | Bandung
 Andi Wijaya  | Surabaya
 Rudi Hartono | Medan
 Rina Putri   | Bandung
(4 rows)

BETWEEN checks a range and is inclusive on both ends:

SELECT title, price
FROM courses
WHERE price BETWEEN 150000 AND 275000;

Output:

              title               | price  
----------------------------------+--------
 SQL Fundamentals                 | 150000
 React for Beginners              | 250000
 Advanced SQL Queries             | 275000
 Docker Essentials for Developers | 200000
(4 rows)

For timestamps, avoid BETWEEN and use a half-open range instead (>= start and < next start). This way you never miss events at 23:59:59.5 or double-count midnight. All orders in August 2025:

SELECT id, user_id, amount, ordered_at
FROM orders
WHERE ordered_at >= '2025-08-01'
  AND ordered_at <  '2025-09-01';

Output:

 id | user_id | amount |       ordered_at       
----+---------+--------+------------------------
  6 |       3 | 300000 | 2025-08-02 21:10:00+00
  7 |       4 | 120000 | 2025-08-04 11:20:00+00
  8 |       4 | 250000 | 2025-08-09 16:00:00+00
  9 |       4 | 400000 | 2025-08-15 13:30:00+00
 10 |       5 | 300000 | 2025-08-18 09:00:00+00
(5 rows)

ORDER BY: sorting results

Without ORDER BY, PostgreSQL returns rows in whatever order is cheapest. That order can change at any time, so always sort when order matters. You can sort by several columns, each with its own direction:

SELECT title, category, price
FROM courses
ORDER BY category ASC, price DESC;

Output:

              title               | category | price  
----------------------------------+----------+--------
 Python for Data Analysis         | data     | 300000
 PostgreSQL Performance Tuning    | database | 350000
 Advanced SQL Queries             | database | 275000
 SQL Fundamentals                 | database | 150000
 Docker Essentials for Developers | devops   | 200000
 Next.js Full Stack               | web      | 400000
 React for Beginners              | web      | 250000
 JavaScript Basics                | web      | 120000
(8 rows)

By default NULL values are treated as larger than any other value, so they come last in ASC and first in DESC. Use NULLS FIRST / NULLS LAST to control this:

SELECT name, city
FROM users
ORDER BY city DESC NULLS LAST;

Output:

     name     |   city   
--------------+----------
 Andi Wijaya  | Surabaya
 Rudi Hartono | Medan
 Maya Sari    | Jakarta
 Dewi Lestari | Jakarta
 Budi Santoso | Jakarta
 Rina Putri   | Bandung
 Siti Rahma   | Bandung
 Agus Salim   | 
(8 rows)

Notice that the three Jakarta users are not in any particular order among themselves. If you need a stable order, add a tie-breaker such as ORDER BY city DESC NULLS LAST, name.

LIMIT, OFFSET and pagination

LIMIT caps the number of rows. Combined with ORDER BY it answers "top N" questions, such as the three most recent orders:

SELECT id, user_id, amount, ordered_at
FROM orders
ORDER BY ordered_at DESC
LIMIT 3;

Output:

 id | user_id | amount |       ordered_at       
----+---------+--------+------------------------
 14 |       1 | 350000 | 2025-09-12 12:00:00+00
 13 |       8 | 400000 | 2025-09-10 15:45:00+00
 12 |       6 | 350000 | 2025-09-03 20:00:00+00
(3 rows)

Offset pagination

For page n with size rows per page, use LIMIT size OFFSET (n - 1) * size. Page 2 with 5 rows per page:

SELECT id, user_id, course_id, status
FROM orders
ORDER BY id
LIMIT 5 OFFSET 5;

Output:

 id | user_id | course_id |  status  
----+---------+-----------+----------
  6 |       3 |         7 | pending
  7 |       4 |         3 | paid
  8 |       4 |         4 | refunded
  9 |       4 |         6 | paid
 10 |       5 |         7 | paid
(5 rows)

Offset pagination is easy, but PostgreSQL still reads and throws away all skipped rows. Page 10,000 is slow, and if rows are inserted while a user is paging, items can shift between pages.

Keyset (cursor) pagination

Instead of skipping rows, remember the last ID you showed and ask for rows after it. If page 2 ended at id = 10, page 3 is:

SELECT id, user_id, course_id, status
FROM orders
WHERE id > 10
ORDER BY id
LIMIT 5;

Output:

 id | user_id | course_id | status  
----+---------+-----------+---------
 11 |       6 |         1 | paid
 12 |       6 |         2 | pending
 13 |       8 |         6 | paid
 14 |       1 |         2 | paid
(4 rows)

This uses the primary key index directly, so it is equally fast on page 3 and page 30,000. Only 4 rows came back, which tells the client there are no more pages. Most infinite-scroll APIs work this way.

DISTINCT: removing duplicates

SELECT DISTINCT city
FROM users
ORDER BY city;

Output:

   city   
----------
 Bandung
 Jakarta
 Medan
 Surabaya
 
(5 rows)

The empty-looking last row is NULL (Agus' city). DISTINCT treats all NULL values as one group.

Common mistakes

• = NULL instead of IS NULL. It never matches anything.
• Pagination without ORDER BY. LIMIT 5 OFFSET 5 without sorting can return overlapping or missing rows between pages.
• BETWEEN on timestamps. BETWEEN '2025-08-01' AND '2025-08-31' stops at midnight on the 31st and misses the rest of that day.
• Forgetting parentheses with OR. a AND b OR c means (a AND b) OR c.
• NOT IN with a list containing NULL. It returns no rows at all. We will see why in Part 7.

Exercise

1. Find all published courses with level intermediate or advanced, cheapest first.
2. Find users whose name contains "ri" (case-insensitive). You should get three people.
3. List orders from July 2025 with status paid, newest first.
4. Write the keyset query for the page after id = 5 with 3 rows per page.

Summary

You learned to filter with comparison operators, AND/OR, IS NULL, LIKE/ILIKE, IN and BETWEEN; to sort with ORDER BY and NULLS LAST; and to paginate with both LIMIT/OFFSET and the faster keyset approach.

In Part 5 we start the intermediate section: turning rows into business numbers with COUNT, SUM, AVG, GROUP BY and HAVING.