How to Use CTE for Pagination in PostgreSQL

Use a WITH clause to define a Common Table Expression (CTE) that selects rows with row numbers using ROW_NUMBER(). Then, filter the CTE by row numbers to get the desired page of results for pagination.

Syntax

The basic syntax for pagination using a CTE in PostgreSQL involves three parts:

• WITH clause: Defines the CTE and assigns row numbers to each row.
• ROW_NUMBER() function: Generates a sequential number for each row based on ordering.
• Filtering: Selects rows where the row number falls within the desired page range.

sql

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY column_name) AS rn
  FROM table_name
)
SELECT * FROM cte
WHERE rn BETWEEN start_row AND end_row;

Example

This example shows how to paginate a list of users, displaying 5 users per page ordered by their id. It fetches page 2 (rows 6 to 10).

sql

WITH user_page AS (
  SELECT id, name, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM users
)
SELECT id, name
FROM user_page
WHERE rn BETWEEN 6 AND 10;

Output

 id |  name  
----+--------
  6 | Alice  
  7 | Bob    
  8 | Carol  
  9 | Dave   
 10 | Eve    
(5 rows)

Common Pitfalls

Common mistakes when using CTE for pagination include:

• Not ordering rows consistently inside ROW_NUMBER(), which leads to unpredictable results.
• Using LIMIT and OFFSET without an ORDER BY, causing inconsistent pages.
• Incorrectly calculating start_row and end_row, resulting in missing or overlapping rows.

Always ensure the ordering column is indexed for better performance.

sql

/* Wrong: No ORDER BY in ROW_NUMBER() */
WITH cte AS (
  SELECT *, ROW_NUMBER() OVER () AS rn
  FROM users
)
SELECT * FROM cte
WHERE rn BETWEEN 1 AND 5;

/* Correct: Specify ORDER BY column */
WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM users
)
SELECT * FROM cte
WHERE rn BETWEEN 1 AND 5;

Quick Reference

Tips for using CTE pagination in PostgreSQL:

• Use ROW_NUMBER() OVER (ORDER BY column) to assign row numbers.
• Calculate page ranges as start_row = (page_number - 1) * page_size + 1 and end_row = page_number * page_size.
• Always include ORDER BY inside ROW_NUMBER() for consistent results.
• CTE pagination is useful for complex queries where LIMIT/OFFSET is less efficient or harder to apply.

Key Takeaways

Use a CTE with ROW_NUMBER() to assign row numbers for pagination.

Always specify ORDER BY inside ROW_NUMBER() for consistent row ordering.

Filter rows in the CTE by row number range to get the desired page.

Calculate start and end row numbers based on page size and number.

CTE pagination helps with complex queries and improves readability.