PostgreSQL Paging Query using LIMIT and OFFSET

In this post, I demonstrate PostgreSQL Pagination or Custom paging query using OFFSET and LIMIT.

Whenever thousands or millions of rows is returning from database at that time paging is required.

Using custom paging we can return only limited data for a particular page.

For example, database stored procedure returned 10,00,00 records at one call. How can you arrange this many data into the application grid or any report.

In this kind of requirement database paging is playing an important role.

The Application has to call stored procedure using two main parameter,

  • Page number
  • Page size

PostgreSQL provides powerful LIMIT and OFFSET features.

LIMIT and OFFSET retire only small portion of rows from a main result set.

Using LIMIT you can set total number of rows which are limited.

Using OFFSET you can skip number of rows which number are defined by OFFESET.

For more details please visit this document.

Now, below is a demonstration of paging stored procedure.

Below, is a created one Employee table with 10 rows.

Below is custom paging stored procedure:
Now execute some sample:
Above sample only return two row at second page, mean row number 3 and 4.


Above sample only return four row at second page, mean row number 5 to 8


Above is simple example of Paging in PostgreSQL using LIMIT and OFFSET.

Please Note:
Whenever you are using LIMIT at that time do not forget to use default ORDER BY which has helped to LIMIT to restricting data into a particular order.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of