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.
