How To Limit The Number Of Rows Returned In PostgreSQL

If you’re working with large datasets in PostgreSQL, you might find yourself needing to limit the number of rows returned in a query. Whether you’re trying to reduce the amount of data you have to work with or simply trying to speed up your queries, limiting the number of rows returned is a useful tool to have in your PostgreSQL toolbox.

In this post, we’ll cover the basics of limiting the number of rows returned in PostgreSQL, including a few different ways to do it and some code examples to help you get started.

Before you keep reading, I just wanted to let you know about our fast, modern PostgreSQL GUI, Beekeeper Studio. Open a table and it pages through the rows for you, no LIMIT needed, and it’s free to use for PostgreSQL, download it here.

The LIMIT Clause

The simplest and most straightforward way to limit the number of rows returned in PostgreSQL is to use the LIMIT clause. The LIMIT clause allows you to specify the maximum number of rows that you want returned from your query.

Here’s an example of how to use the LIMIT clause:

SELECT * FROM customers LIMIT 10;

This query will return the first 10 rows from the customers table. Note that the order in which the rows are returned depends on the order specified in the ORDER BY clause (if one is present). If no ORDER BY clause is present, the order of the rows is undefined.

Using LIMIT With OFFSET

In addition to the LIMIT clause, you can also use the OFFSET clause to skip a specified number of rows before starting to return results. For example:

SELECT * FROM customers LIMIT 10 OFFSET 20;

This query will return the next 10 rows after skipping the first 20 rows in the customers table.

Pagination With LIMIT and OFFSET

By combining the LIMIT and OFFSET clauses, you can implement paging and navigate through your data in a more efficient way. For example:

SELECT * FROM customers LIMIT 10 OFFSET (10 * (current_page - 1));

This query will return 10 rows for each page, with the current_page variable determining which page to return. You can change the value of current_page to navigate through different pages of data.

Limiting Rows Based on a Condition

In some cases, you might want to limit the number of rows returned based on a condition. For example, you might want to return the top 10 customers with the highest sales. To do this, you can use a subquery:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_num
  FROM customers
) subquery
WHERE row_num <= 10;

This query uses the ROW_NUMBER() function to assign a unique number to each row in the result set, based on the sales column. The outer query then filters the rows to only include those with a row_num of 10 or less.

Conclusion

Limiting the number of rows returned in PostgreSQL is a useful technique for managing large datasets. Whether you’re trying to reduce the amount of data you have to work with, speed up your queries, or implement paging, the LIMIT and OFFSET clauses provide a simple and flexible way to achieve your goals. And by combining them with other techniques such as subqueries, you can implement even more complex and sophisticated solutions.

While LIMIT clauses are essential for working with large datasets in SQL, Beekeeper Studio offers an alternative approach with its paginated table view - browse through results page by page without writing any SQL, and apply quick filters to narrow down your data visually when exploring tables.