Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Setting Row Range with LIMIT and OFFSET

PostgreSQL enforces no limit upon the number of rows retrievable from a SQL query. If you attempt to execute a query that returns several million rows, it may take a while, but the server will not stop until it has returned the entire result set (or until it is interrupted).

Applications could conceivably be written to programmatically "page" through large sets of data after retrieval, but SQL provides as a convenience the LIMIT and OFFSET clauses, which allow for the retrieval of a specified portion of the generated result set.

When the LIMIT clause is specified, no more than the requested number of rows will be returned (though there may be fewer if the result set is smaller than the passed parameter). When the OFFSET clause is specified, it skips the number of rows defined by its parameters before returning rows. If both are specified, the number of rows to be included as per the LIMIT clause will not be counted until the number of rows dictated by the OFFSET clause have been skipped.

Example 4-45. Using LIMIT and OFFSET

booktown=# 
SELECT isbn, title, publication

booktown-# 
       FROM editions NATURAL JOIN books AS b (book_id)

booktown-# 
       ORDER BY publication DESC

booktown-# 
       LIMIT 5;

    isbn    |         title         | publication
------------+-----------------------+-------------
 0596000855 | Programming Python    | 2001-03-01
 0451457994 | 2001: A Space Odyssey | 2000-09-12
 0451198492 | 2001: A Space Odyssey | 1999-10-01
 044100590X | Dune                  | 1999-10-01
 0929605942 | The Tell-Tale Heart   | 1998-12-01
(5 rows)

booktown=# 
SELECT isbn, title, publication

booktown-# 
       FROM editions NATURAL JOIN books AS b (book_id)

booktown-# 
       ORDER BY publication DESC

booktown-# 
       LIMIT 5

booktown-# 
       OFFSET 2;

    isbn    |         title         | publication
------------+-----------------------+-------------
 0451198492 | 2001: A Space Odyssey | 1999-10-01
 044100590X | Dune                  | 1999-10-01
 0929605942 | The Tell-Tale Heart   | 1998-12-01
 0441172717 | Dune                  | 1998-09-01
 1885418035 | The Tell-Tale Heart   | 1995-03-28
(5 rows)

Example 4-45 demonstrates, in the first query, a simple use of LIMIT, by retrieving only 5 rows from the joined set of the editions and books table. Ordinarily, such a join would result in 17 rows.

The second query in Example 4-45 shows the use of the OFFSET clause, to shift the scope of the result set down by two rows. You can see that the last three rows of the first query's result set overlap with the first three rows of the second query's result set. The ORDER BY clause in each of these queries insures the consistency of the sets returned.

Note: The ORDER BY clause can be a helpful tool for making sure that the results of a limited query are relevant. This is because sorting occurs before limiting, allowing you to determine which rows end up being limited.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire