How to use limit and offset in PostgreSQL (2024)

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.

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_listFROM 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=# \xExpanded display is on.limit_offset_demo=# select count(*) from public."Album" ;-[ RECORD 1 ]count | 306limit_offset_demo=# select * from public."Album" order by "AlbumId" offset 300 limit 1;-[ RECORD 1 ]--------------------------------------------------------AlbumId | 342Title | Locatelli: Concertos for Violin, Strings and Continuo, Vol. 3ArtistId | 271limit_offset_demo=# select * from public."Album" order by "AlbumId" offset 300 limit 2;-[ RECORD 1 ]-------------------------------------------------------AlbumId | 342Title | Locatelli: Concertos for Violin, Strings and Continuo, Vol. 3ArtistId | 271-[ RECORD 2 ]--------------------------------------------------------AlbumId | 343Title | Respighi:Pines of RomeArtistId | 226limit_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=# \xExpanded display is on.limit_offset_demo=# select * from public."Album" limit 1 offset 1;-[ RECORD 1 ]---------------AlbumId | 2Title | Balls to the WallArtistId | 2Result with Order by using column “Title”limit_offset_demo=# select * from public."Album" order by "Title" limit 1 offset 1;-[ RECORD 1]---------------------------------------------------------AlbumId | 257Title | 20th Century Masters - The Millennium Collection: The Best of ScorpionsArtistId | 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

​