Understanding LIMIT in PostgreSQL (With Examples)

Written by Team Timescale

When working with large datasets in PostgreSQL (or TimescaleDB), limiting the number of rows returned by a query is often useful. This limitation can be particularly helpful for improving performance and managing the amount of data your application needs to handle at once. The LIMIT clause in PostgreSQL is designed to do just that.

In this post, we'll explore the LIMIT clause, understand its importance, and see how you can use it with practical examples.

What Is the LIMIT Clause in PostgreSQL?

The LIMIT clause in PostgreSQL specifies the maximum number of rows that a query should return. It's commonly used for pagination, where results are divided into pages, or simply to fetch a subset of rows for analysis or display purposes.

Syntax of the LIMIT clause

The basic syntax of the LIMIT clause is as follows:

SELECT column1, column2, ... FROM table LIMIT number_of_rows;

Optionally, you can use the OFFSET clause to skip a specified number of rows before beginning to return rows:

SELECT column1, column2, ... FROM table LIMIT number_of_rows OFFSET skip_rows;

Example table

Let's consider a table employees with the following structure and data:

CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary NUMERIC );

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Engineering', 70000), ('Bob', 'HR', 50000), ('Carol', 'Engineering', 75000), ('Dave', 'Marketing', 60000), ('Eve', 'Engineering', 72000), ('Frank', 'HR', 52000), ('Grace', 'Marketing', 58000), ('Hank', 'Engineering', 69000), ('Ivy', 'HR', 51000), ('Jack', 'Marketing', 62000);

Practical examples with LIMIT

Let's dive into some examples to see the LIMIT clause in action.

Example 1: Limiting the number of rows returned

Suppose we want to retrieve the first five employees from the employees table. We can use the LIMIT clause to achieve this:

SELECT id, name, department, salary FROM employees LIMIT 5;

Result:

id | name | department | salary ----+-------+------------+-------- 1 | Alice | Engineering| 70000 2 | Bob | HR | 50000 3 | Carol | Engineering| 75000 4 | Dave | Marketing | 60000 5 | Eve | Engineering| 72000

This query returns the first five rows from the employees table.

Example 2: Using LIMIT with OFFSET Now, let's say we want to retrieve the next set of five employees, effectively paginating our results. We can use the OFFSET clause along with LIMIT:

SELECT id, name, department, salary FROM employees LIMIT 5 OFFSET 5;

Result:

id | name | department | salary ----+-------+------------+-------- 6 | Frank | HR | 52000 7 | Grace | Marketing | 58000 8 | Hank | Engineering| 69000 9 | Ivy | HR | 51000 10 | Jack | Marketing | 62000

This query skips the first five rows and returns the next five rows from the employees table.

Example 3: Combining LIMIT with ORDER BY

In many cases, you may want to order the results before limiting the number of rows. For instance, to get the top three highest-paid employees, you can combine ORDER BY with LIMIT:

SELECT id, name, department, salary FROM employees ORDER BY salary DESC LIMIT 3;

Result:

This query orders the employees by their salary in descending order and returns the top three highest-paid employees.

Conclusion

The LIMIT clause is a valuable tool in PostgreSQL for controlling the number of rows a query returns. Whether you're implementing pagination, fetching a subset of rows for display, or simply improving query performance, understanding and utilizing the LIMIT clause can significantly enhance your ability to manage and analyze data efficiently.

To learn more about how to use this clause, check the PostgreSQL documentation. To learn more about other PostgreSQL clauses and basics, visit the Postgres basics section.