FETCH FIRST for SQL standard pagination in PostgreSQL - Mini Project: Build & Apply

Using FETCH FIRST for SQL Standard Pagination

📖 Scenario: You are managing a small bookstore database. You want to show the list of books to customers page by page, so they can easily browse without being overwhelmed by too many books at once.

🎯 Goal: Build a SQL query that uses the FETCH FIRST clause to limit the number of rows returned, simulating pagination.

📋 What You'll Learn

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

Insert exactly 5 books with specified titles and authors.

Write a query to select all columns from books ordered by id ascending.

Add a configuration variable page_size to control how many rows to fetch.

Use FETCH FIRST with page_size to limit the number of rows returned.

💡 Why This Matters

🌍 Real World

Pagination is used in websites and apps to show data in small chunks, improving user experience and performance.

💼 Career

Knowing how to paginate data with SQL is essential for backend developers, data analysts, and anyone working with databases to build efficient and user-friendly applications.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and author (text). Then insert these 5 books exactly: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), (3, 'Pride and Prejudice', 'Jane Austen'), (4, 'To Kill a Mockingbird', 'Harper Lee'), and (5, 'The Great Gatsby', 'F. Scott Fitzgerald').

PostgreSQL

-- Create the books table and insert 5 rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the books.

2

Set the page size variable

Create a variable called page_size and set it to 3. This will control how many books to show per page.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen'),
(4, 'To Kill a Mockingbird', 'Harper Lee'),
(5, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Set the page_size variable
-- Your code here

Need a hint?

In PostgreSQL psql shell, use \set page_size 3 to define a variable. If using another tool, just note the variable name and value.

3

Write the query to select books ordered by id

Write a SQL query to select all columns from books ordered by id ascending.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen'),
(4, 'To Kill a Mockingbird', 'Harper Lee'),
(5, 'The Great Gatsby', 'F. Scott Fitzgerald');

\set page_size 3

-- Write the SELECT query ordered by id
-- Your code here

Need a hint?

Use SELECT with ORDER BY id ASC to get the books sorted by their id.

4

Add FETCH FIRST clause to limit rows

Add FETCH FIRST with page_size rows only to the query to limit the number of books returned per page.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'J.R.R. Tolkien'),
(2, '1984', 'George Orwell'),
(3, 'Pride and Prejudice', 'Jane Austen'),
(4, 'To Kill a Mockingbird', 'Harper Lee'),
(5, 'The Great Gatsby', 'F. Scott Fitzgerald');

\set page_size 3

SELECT id, title, author FROM books ORDER BY id ASC
-- Add FETCH FIRST clause here
-- Your code here

Need a hint?

Use FETCH FIRST :page_size ROWS ONLY to limit the rows returned to the value of page_size.