LIMIT and OFFSET pagination in PostgreSQL - Mini Project: Build & Apply

LIMIT and OFFSET Pagination in PostgreSQL

📖 Scenario: You are managing a database for an online bookstore. The books table contains many book records. To show books to users in pages, you want to fetch a limited number of books at a time.

🎯 Goal: Build SQL queries using LIMIT and OFFSET to paginate the books table results. You will first create the table and insert sample data, then write queries to get specific pages of books.

📋 What You'll Learn

Create a books table with columns id (integer) and title (text).

Insert exactly 10 books with ids 1 to 10 and titles 'Book 1' to 'Book 10'.

Create a variable page_size set to 3 to control how many books show per page.

Write a query to select the first page of books using LIMIT and OFFSET.

Write a query to select the second page of books using LIMIT and OFFSET.

💡 Why This Matters

🌍 Real World

Pagination is used in websites and apps to show large lists of items in small pages, like product lists or search results.

💼 Career

Database developers and backend engineers use LIMIT and OFFSET to efficiently fetch data for user interfaces.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer) and title (text). Then insert 10 rows with id from 1 to 10 and title as 'Book 1' to 'Book 10'.

PostgreSQL

-- Create the books table and insert 10 books
-- Your code here

Hint

Use CREATE TABLE to make the table. Use one INSERT INTO statement with multiple rows.

2

Set the page size variable

Declare a variable called page_size and set it to 3. This will control how many books show on each page.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Book 1'),
(2, 'Book 2'),
(3, 'Book 3'),
(4, 'Book 4'),
(5, 'Book 5'),
(6, 'Book 6'),
(7, 'Book 7'),
(8, 'Book 8'),
(9, 'Book 9'),
(10, 'Book 10');

-- Declare page_size variable
-- Your code here

Hint

In psql, use \set page_size 3 to create a variable.

3

Write query for the first page of books

Write a SQL query to select id and title from books ordered by id. Use LIMIT with the variable :page_size and OFFSET 0 to get the first page.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Book 1'),
(2, 'Book 2'),
(3, 'Book 3'),
(4, 'Book 4'),
(5, 'Book 5'),
(6, 'Book 6'),
(7, 'Book 7'),
(8, 'Book 8'),
(9, 'Book 9'),
(10, 'Book 10');

\set page_size 3

-- Write query for first page
-- Your code here

Hint

Use LIMIT :page_size and OFFSET 0 to get the first page.

4

Write query for the second page of books

Write a SQL query to select id and title from books ordered by id. Use LIMIT :page_size and OFFSET :page_size to get the second page of books.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'Book 1'),
(2, 'Book 2'),
(3, 'Book 3'),
(4, 'Book 4'),
(5, 'Book 5'),
(6, 'Book 6'),
(7, 'Book 7'),
(8, 'Book 8'),
(9, 'Book 9'),
(10, 'Book 10');

\set page_size 3

SELECT id, title FROM books ORDER BY id LIMIT :page_size OFFSET 0;

-- Write query for second page
-- Your code here

Hint

Use OFFSET :page_size to skip the first page and get the second page.

Practice

1. What does the LIMIT clause do in a PostgreSQL query?

easy

A. It sorts the rows in ascending order.

B. It controls how many rows the query returns.

C. It skips a number of rows before returning results.

D. It groups rows based on a column.

Solution

1. Step 1: Understand the role of LIMIT

   The LIMIT clause restricts the number of rows returned by a query to a specified count.

2. Step 2: Differentiate from OFFSET

   OFFSET skips rows, but LIMIT controls the count of rows returned after skipping.

3. Final Answer:

   It controls how many rows the query returns. -> Option B

4. Quick Check:

   LIMIT = row count control [OK]

Hint: LIMIT sets max rows returned, OFFSET skips rows first [OK]

Common Mistakes:

• Confusing LIMIT with OFFSET
• Thinking LIMIT sorts rows
• Assuming LIMIT groups rows

2. Which of the following is the correct syntax to get 10 rows starting from the 6th row in PostgreSQL?

easy

A. SELECT * FROM table LIMIT 10 OFFSET 5;

B. SELECT * FROM table LIMIT 5 OFFSET 10;

C. SELECT * FROM table LIMIT 6 OFFSET 10;

D. SELECT * FROM table OFFSET 10 LIMIT 6;

Solution

1. Step 1: Understand OFFSET and LIMIT order

   OFFSET 5 skips first 5 rows, LIMIT 10 returns next 10 rows.

2. Step 2: Check syntax correctness

   PostgreSQL allows LIMIT then OFFSET; LIMIT 10 OFFSET 5 is correct.

3. Final Answer:

   SELECT * FROM table LIMIT 10 OFFSET 5; -> Option A

4. Quick Check:

   LIMIT 10 OFFSET 5 = 10 rows after skipping 5 [OK]

Hint: Use LIMIT first, then OFFSET for correct syntax [OK]

Common Mistakes:

• Swapping LIMIT and OFFSET values
• Using OFFSET larger than total rows
• Incorrect order of LIMIT and OFFSET

3. Given the table employees with 15 rows ordered by id, what rows will this query return?

SELECT * FROM employees ORDER BY id LIMIT 5 OFFSET 10;

medium

A. Rows with id 11 to 15

B. Rows with id 6 to 10

C. Rows with id 1 to 5

D. Rows with id 10 to 14

Solution

1. Step 1: Understand OFFSET 10

   OFFSET 10 skips the first 10 rows (id 1 to 10).

2. Step 2: LIMIT 5 after skipping

   LIMIT 5 returns the next 5 rows, which are id 11 to 15.

3. Final Answer:

   Rows with id 11 to 15 -> Option A

4. Quick Check:

   OFFSET 10 + LIMIT 5 = rows 11-15 [OK]

Hint: OFFSET skips, LIMIT picks next rows [OK]

Common Mistakes:

• Counting OFFSET as inclusive
• Mixing row numbers with id values
• Assuming OFFSET starts at 1

4. Identify the error in this query:

SELECT * FROM products LIMIT OFFSET 5 10;

medium

A. LIMIT and OFFSET order is reversed.

B. OFFSET value must come before LIMIT value.

C. Syntax error: LIMIT must have a number before OFFSET.

D. No error, query is valid.

Solution

1. Step 1: Analyze LIMIT and OFFSET syntax

   LIMIT must be followed by a number, then OFFSET with its number.

2. Step 2: Check given query

   Query has 'LIMIT OFFSET 5 10' which is invalid syntax; LIMIT lacks a number before OFFSET.

3. Final Answer:

   Syntax error: LIMIT must have a number before OFFSET. -> Option C

4. Quick Check:

   LIMIT needs number before OFFSET [OK]

Hint: Always put LIMIT number before OFFSET number [OK]

Common Mistakes:

• Writing OFFSET before LIMIT number
• Omitting numbers after LIMIT or OFFSET
• Assuming order doesn't matter

5. You want to display page 3 of a product list, showing 20 products per page. Which query correctly uses LIMIT and OFFSET?

hard

A. SELECT * FROM products LIMIT 40 OFFSET 20;

B. SELECT * FROM products LIMIT 60 OFFSET 20;

C. SELECT * FROM products LIMIT 20 OFFSET 60;

D. SELECT * FROM products LIMIT 20 OFFSET 40;

Solution

1. Step 1: Calculate OFFSET for page 3

   Page 3 means skipping first 2 pages: 2 * 20 = 40 rows to skip.

2. Step 2: Set LIMIT to page size

   LIMIT is number of rows per page, so LIMIT 20.

3. Step 3: Form the query

   Use LIMIT 20 OFFSET 40 to get page 3 products.

4. Final Answer:

   SELECT * FROM products LIMIT 20 OFFSET 40; -> Option D

5. Quick Check:

   Page 3 offset = 40, limit = 20 [OK]

Hint: OFFSET = (page-1)*page_size, LIMIT = page_size [OK]

Common Mistakes:

• Using OFFSET as page number
• Swapping LIMIT and OFFSET values
• Calculating OFFSET incorrectly