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
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...