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.
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 | 179In 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 doesnt 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::
//www.postgresql.org/docs/12/queries-limit.html
//www.enterprisedb.com/edb-docs/d/postgresql/reference/manual/12.1/queries-limit.html
- Connecting PostgreSQL using psql and pgAdmin
- How to use PostgreSQL with Django
- 10 Examples of PostgreSQL Stored Procedures
- How to use PostgreSQL with Laravel
- How to use tables and column aliases...
- PostgreSQL vs. SQL Server [MSSQL]...
- The Complete Oracle to PostgreSQL Migration...
- PostgreSQL vs. MySQL: A 360-degree Comparison...
- PostgreSQL Replication and Automatic Failover...
- Postgres on Kubernetes or VMs: A Guide...
- Postgres Tutorials
- The EDB Blog
- White Papers
- The EDB Docs
- Webinars