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

Need a 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

Need a 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

Need a 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

Need a hint?

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