How to use limit and offset in PostgreSQL

December 18, 2019

SUMMARY: This article covers LIMIT and OFFSET keywords in PostgreSQL. It provides definitions for both as well as 5 examples of how they can be used and tips and tricks.

1. LIMIT and OFFSET 

2. Examples

3. Tips and Tricks

LIMIT and OFFSET 

LIMIT and OFFSET are used when you want to retrieve only a few records from your result of query. 

LIMIT will retrieve only the number of records specified after the LIMIT keyword, unless the query itself returns fewer records than the number specified by LIMIT.

OFFSET is used to skip the number of records from the results.

Syntax,



SELECT select_list

    FROM table_expression

    [ ORDER BY ... ]

    [ LIMIT { number | ALL } ] [ OFFSET number ]

 

Examples

Example 1

Using a simple LIMIT clause to limit the number of records. 

When using LIMIT, it is important to add an ORDER BY clause that constrains the resulting rows into a specific order. Otherwise you will get an unpredictable subset of the query's rows. The ordering is unknown unless you specify it with ORDER BY.

limit_offset_demo=# select count(*) from public."Album";

 count

-------

   306

(1 row)



limit_offset_demo=# select * from public."Album" limit 4;

 AlbumId |             Title             | ArtistId

---------+---------------------------------------+----------

    1   | For Those About To Rock We Salute You |    1

    2   | Balls to the Wall                 |    2

    3   | Restless and Wild                 |    2

    4   | Let There Be Rock                 |    1

(4 rows)



limit_offset_demo=#

 

In the above example, the table “Album” has 306 records. Adding the LIMIT clause with the number 4 will return only 4 records from the total 306 in the table. The ordering of the 4 rows is unknown because an ORDER BY clause was not included. Since above it mentions that its important to include order by.

Example 2 

Using the OFFSET clause to begin at a specific record and leave out the rows that come before. 

limit_offset_demo=# select * from public."MediaType" ;

 MediaTypeId |        Name         

-------------+-----------------------------

        1 | MPEG audio file

        2 | Protected AAC audio file

        3 | Protected MPEG-4 video file

        4 | Purchased AAC audio file

        5 | AAC audio file

(5 rows)



limit_offset_demo=# select * from public."MediaType" offset 3;

 MediaTypeId |       Name       

-------------+--------------------------

        4 | Purchased AAC audio file

        5 | AAC audio file

(2 rows)

limit_offset_demo=#

 

In the above example, the table “MediaType” has 5 records. By using OFFSET with the number 3, we are able to skip the first 3 records and displaying only the remaining ones.

Example 3

Using LIMIT and OFFSET in the same query. 

limit_offset_demo=# \x

Expanded display is on.

limit_offset_demo=# select count(*) from public."Album" ;

-[ RECORD 1 ]

count | 306



limit_offset_demo=# select *  from public."Album" order by "AlbumId" offset 300 limit 1;

-[ RECORD 1 ]--------------------------------------------------------

AlbumId  | 342

Title | Locatelli: Concertos for Violin, Strings and Continuo, Vol. 3

ArtistId | 271



limit_offset_demo=# select *  from public."Album" order by "AlbumId" offset 300 limit 2;

-[ RECORD 1 ]-------------------------------------------------------

AlbumId  | 342

Title | Locatelli: Concertos for Violin, Strings and Continuo, Vol. 3

ArtistId | 271

-[ RECORD 2 ]--------------------------------------------------------

AlbumId  | 343

Title | Respighi:Pines of Rome

ArtistId | 226



limit_offset_demo=#

 

We can use the LIMIT and OFFSET clauses together to change the number of records to display. The example above shows that table “Album” has 306 records. OFFSET skips the first 300 records, and then LIMIT 1 and 2 place limits on the returning rows that are displayed.

Example 4

Using LIMIT, OFFSET, and ORDER BY clause for returning specific records.

limit_offset_demo=# \x

Expanded display is on.

limit_offset_demo=# select *  from public."Album"  limit 1 offset 1;

-[ RECORD 1 ]---------------

AlbumId  | 2

Title | Balls to the Wall

ArtistId | 2



Result with Order by using column “Title”



limit_offset_demo=# select *  from public."Album" order by "Title" limit 1 offset 1;

-[ RECORD 1]---------------------------------------------------------

AlbumId  | 257

Title | 20th Century Masters - The Millennium Collection: The Best of Scorpions

ArtistId | 179

 

In this example ORDER BY is used to return different results from the same LIMIT 1 OFFSET 1 query.

Example 5 

If the return query itself doesn’t have the sufficient number of rows specified by LIMIT, then it will return the total number of rows for that query.

limit_offset_demo=# select * from public."MediaType";



 MediaTypeId |        Name         

-------------+-----------------------------

        1 | MPEG audio file

        2 | Protected AAC audio file

        3 | Protected MPEG-4 video file

        4 | Purchased AAC audio file

        5 | AAC audio file

(5 rows)



limit_offset_demo=# select * from public."MediaType" limit 10;



 MediaTypeId |        Name         

-------------+-----------------------------

        1 | MPEG audio file

        2 | Protected AAC audio file

        3 | Protected MPEG-4 video file

        4 | Purchased AAC audio file

        5 | AAC audio file

(5 rows)



limit_offset_demo=#

 

Tips And Tricks

1. LIMIT 0 can be used in situations where you just want to know what are the columns available in the table.

Example

limit_offset_demo=# select * from public."MediaType" limit 0;

 MediaTypeId | Name

-------------+------

(0 rows)



limit_offset_demo=#

 

2. If you use the LIMIT clause with ALL, then it will display all the available records in the table. It functions the same as omitting or ignoring the LIMIT clause.

Example

limit_offset_demo=# select count(*) from public."MediaType" ;

 count

-------

  5

(1 row)



limit_offset_demo=# select * from public."MediaType" limit ALL;

 MediaTypeId |        Name         

-------------+-----------------------------

        1 | MPEG audio file

        2 | Protected AAC audio file

        3 | Protected MPEG-4 video file

        4 | Purchased AAC audio file

        5 | AAC audio file

(5 rows)



limit_offset_demo=#

 

3. If you use OFFSET clause with number 0 then it will display all the available records in the table. It functions the same as omitting or ignoring the OFFSET clause.

Example

limit_offset_demo=# select count(*) from public."MediaType" ;

 count

-------

  5

(1 row)



limit_offset_demo=# select * from public."MediaType" OFFSET 0;

 MediaTypeId |        Name         

-------------+-----------------------------

        1 | MPEG audio file

        2 | Protected AAC audio file

        3 | Protected MPEG-4 video file

        4 | Purchased AAC audio file

        5 | AAC audio file

(5 rows)



limit_offset_demo=#

 

Reference Links::

https://www.postgresql.org/docs/12/queries-limit.html

https://www.enterprisedb.com/edb-docs/d/postgresql/reference/manual/12.1/queries-limit.html