PostgreSQL OFFSET - Skip the specified number of rows

In PostgreSQL, you can use the OFFSET clause in a SELECT statement to skip a specified number of rows.

PostgreSQL OFFSET Syntax

The PostgreSQL OFFSET is an optional clause of the SELECT statement that skips the query by the specified number of rows.

This is the syntax of the OFFSET clause:

OFFSET skipped_rows

Here, the skipped_rows is the number of rows to skip. For example OFFSET 10, it means to skip 10 rows.

The full usage of a statement with a OFFSET clause is as follows:SELECT

SELECT column_list
FROM table_name
[other_clauses]
OFFSET skipped_rows;

other_clauses Indicates other clauses that can be used in the SELECT statement, such as WHERE, ORDER BY, LIMIT, and FETCH etc.

Typically, you need to use SELECT with OFFSET and ORDER BY so that you get a result set sorted in the specified order. This is a good practice.

In some applications of paginated queries, you need to use LIMIT (or FETCH) and OFFSET in the SELECT statement as follows:

SELECT column_list
FROM table_name
ORDER BY ...
LIMIT rows_count OFFSET skipped_rows;

For example:

• The first page can use LIMIT 10 OFFSET 0 that indicates that a maximum of 10 rows are returned.
• The second page can use LIMIT 10 OFFSET 10 that indicates that a maximum of 10 rows are returned after skipping the 10 rows of the first page.
• The third page can be used LIMIT 10 OFFSET 20 that indicates that a maximum of 10 rows are returned after skipping the 20 rows of the first two pages.
• And so on…

PostgreSQL OFFSET Examples

We will use the film table from the PostgreSQL Sakila sample database to demonstrate the use of PostgreSQL OFFSET.

Basic example of PostgreSQL OFFSET

To skip the first 995 rows when querying from the film table, use the following SELECT statement with OFFSET:

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
OFFSET 995;

 film_id |       title       | release_year
---------+-------------------+--------------
     996 | YOUNG LANGUAGE    |         2006
     997 | YOUTH KICK        |         2006
     998 | ZHIVAGO CORE      |         2006
     999 | ZOOLANDER FICTION |         2006
    1000 | ZORRO ARK         |         2006

Example of paging query using PostgreSQL LIMIT and OFFSET

There are 1000 rows of information about the movie in the film table. You can verify this with the following SELECT statement with an expression COUNT(*):

SELECT COUNT(*) FROM film;

 count
-------
  1000

Suppose you have a system that needs to display all the films on the front end. It is not a good solution to display all 1000 rows on one page. Because of the following disadvantages of this solution:

• The database performance. Returning a large amount of data in one statement will bring greater memory overhead and IO consumption to the database server.
• The application performance. A large amount of data will cause the application to occupy more memory, and even cause the application to freeze or down.
• The user experience. Users may get dizzy when faced with a large amount of rows.

A better solution is to display all the films in pagination. You can easily implement paginated queries using the SELECT statement with LIMIT and OFFSET clauses.

Suppose you need to display 10 films per page, then you can use the following statement to get all the rows of the first page:

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
LIMIT 10;

Here, in order to keep the order of all pagination consistent, we use ORDER BY film_id to sort the films by film_id, and use LIMIT 10 to limit this query to return a maximum of 10 rows.

 film_id |      title       | release_year
---------+------------------+--------------
       1 | ACADEMY DINOSAUR |         2006
       2 | ACE GOLDFINGER   |         2006
       3 | ADAPTATION HOLES |         2006
       4 | AFFAIR PREJUDICE |         2006
       5 | AFRICAN EGG      |         2006
       6 | AGENT TRUMAN     |         2006
       7 | AIRPLANE SIERRA  |         2006
       8 | AIRPORT POLLOCK  |         2006
       9 | ALABAMA DEVIL    |         2006
      10 | ALADDIN CALENDAR |         2006

To get the 10 rows to display on the second page, we use the OFFSET 10 clause to skip the 10 rows on the first page and use LIMIT 10 to limit the query to return a maximum of 10 rows using. You can get all the rows of the second page using a statement like this:

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

 film_id |        title        | release_year
---------+---------------------+--------------
      11 | ALAMO VIDEOTAPE     |         2006
      12 | ALASKA PHANTOM      |         2006
      13 | ALI FOREVER         |         2006
      14 | ALICE FANTASIA      |         2006
      15 | ALIEN CENTER        |         2006
      16 | ALLEY EVOLUTION     |         2006
      17 | ALONE TRIP          |         2006
      18 | ALTER VICTORY       |         2006
      19 | AMADEUS HOLY        |         2006
      20 | AMELIE HELLFIGHTERS |         2006

Similarly, you can use the following statement to get all the rows of the third page:

SELECT
  film_id,
  title,
  release_year
FROM film
ORDER BY film_id
OFFSET 20
LIMIT 10;

Here, the OFFSET 20 indicates to skip the 20 rows of the first two pages, and the LIMIT 10 limits this query to return a maximum of 10 rows.

Conclusion

In this article, you learned how to use a PostgreSQL OFFSET clause to skip a specified number of rows. LIMIT, OFFSET and ORDER BY clauses are often used to solve paginated query problems.