PostgreSQL LIMIT with OFFSET clause (original) (raw)

Last Updated : 12 Jul, 2025

The **PostgreSQL LIMIT clause is a powerful feature that allows users to retrieve a specific subset of rows from query results. This optional clause can be paired with the **OFFSET clause to skip a specified number of rows before returning the desired results. Such functionality is particularly beneficial for **pagination, enabling us to fetch data in **manageable chunks.

In this article, we will explain the PostgreSQL **LIMIT and **OFFSET clauses in-depth, with **practical examples and outputs to illustrate their application.

PostgreSQL LIMIT with OFFSET Clause

The **LIMIT clause restricts the number of rows returned by a **PostgreSQL query. By specifying a number after the **LIMIT keyword, we train PostgreSQL to return only that many rows. This is particularly **advantageous when working with **large tables where retrieving every row could lead to **performance issues, increased loading times, and an overcrowded user interface.

**Syntax

SELECT * FROM table LIMIT n OFFSET m;

**Key Terms

• The **LIMIT clause returns a subset of "n" rows from the query result.
• The **OFFSET clause placed after the LIMIT clause skips "m" number of rows before returning the result query.
• If "**m" is **Zero, then it acts as a normal **LIMIT clause.

PostgreSQL LIMIT with OFFSET Clause Examples

Now, let us examine a few practical examples of using the **LIMIT and **OFFSET clauses in **PostgreSQL to better understand their application. PostgreSQL **LIMIT with **OFFSET clause examples provide valuable insights into how to efficiently manage large datasets and implement **pagination in our queries

**Example 1: Fetching Films with LIMIT and OFFSET

Here we will query for 5 films starting from the seventh one ordered by '**film_id' from the '**film' table of our sample database.

**Query:

SELECT
film_id,
title,
release_year
FROM
film
ORDER BY
film_id
LIMIT 5 OFFSET 6;

**Output

**Explanation:

This query retrieves 5 films starting from the seventh record (offset 6) in the table, ordered by their 'film_id'.

Example 2: Fetching Films Ordered by Title in Descending Order

Here we will query for 5 films starting from the seventh one ordered by '**film_id' from the film table of our sample database in descending order of the film '**title'.

**Query:

SELECT
film_id,
title,
release_year
FROM
film
ORDER BY
title DESC
LIMIT 5 OFFSET 6;

**Output

**Explanation:

This query retrieves 5 films starting from the seventh record (offset 6) in the table, ordered by their '**title'**in descending order.

Important Points About PostgreSQL LIMIT with OFFSET Clause

• The LIMIT clause restricts the number of rows returned by a query. The OFFSET clause skips a specified number of rows before beginning to return rows from the query.
• An OFFSETof zero (OFFSET 0) effectively ignores the **OFFSET**clause, making the query act as a standard LIMIT query.
• **LIMIT**and **OFFSET**are commonly used for pagination in applications, allowing users to retrieve data in smaller, manageable chunks.
• An alternative to **LIMIT**and **OFFSET**is keyset pagination, which uses a **WHERE clause to filter rows based on a specific key.
• PostgreSQL also supports the FETCH clause as an alternative to LIMIT, which can be more readable in some contexts.

Conclusion

Understanding the LIMIT and **OFFSET clauses in **PostgreSQL is important for effective data retrieval, especially when working with **large datasets. By utilizing these clauses, you can implement efficient **pagination strategies in your applications, enhancing the overall user experience. Whether we are fetching a limited number of results or skipping records, mastering the use of **LIMIT and **OFFSET can significantly improve our **SQL querying capabilities.