In this tutorial, we will be learning about the LIMIT and OFFSET keywords in postgresql which are used to control the number of records in the output. Both terms are super easy, so be with us and without wasting the time, let’s get straight into the topic.
The PostgreSQL LIMIT and OFFSET clauses are used to control the number of records returned by a query. The LIMIT clause specifies the maximum number of records to return, while the OFFSET clause allows you to skip a certain number of records before starting to return them. These clauses can be used independently or together. For example,
SELECT * FROM table LIMIT 5 OFFSET 2would skip the first two records and then return the next five.
Understanding PostgreSQL’s LIMIT and OFFSET
The LIMIT and OFFSET clauses are used to return a limited number of records even if the query can return more records. For example, if you want to see the students having marks greater than 90, but you only want to see the first 5 students. In this case, you can use the LIMIT clause.
Note that, LIMIT and OFFSET can be used independently as well as together depending upon the use case.
In simpler terms:
- The LIMIT clause applies the limit to the number of records. For example, limiting the output to only 5, 10, 15 or anything you want.
- The OFFSET clause, on the other hand, is used to skip a particular number of records. In another way, the OFFSET will pick the records from the mentioned count. For example, if the query returns 10 records but you want to skip the first 5 records and only show the next 5, you can use the OFFSET.
By now, you should have a clear understanding about the LIMIT and the OFFSET clauses. Let’s now see their syntax and head towards examples.
Syntax of LIMIT and OFFSET in PostgreSQL
Below is the syntax of LIMIT and OFFSET in postgresql.
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, you can see that the LIMIT and OFFSET can be used independently as both are present in different brackets.
When you specify the number for the LIMIT clause, the query will return not more than the specified limit. However, the number of returned rows can be fewer if the query itself fetches fewer rows. For example, if there are only 4 records present in the table for the given query, the LIMIT 5 will also show only 4 records.
On the other hand, LIMIT ALL is the same as not mentioning the LIMIT clause.
The OFFSET will skip the number of specified rows before returning the rows. Note that, the OFFSET 0 is the same as not mentioning the OFFSET clause.
You can have both LIMIT and OFFSET clauses in a single query. In such case, the query will skip the mentioned rows in the OFFSET and then the LIMIT clause will apply the limits to the returned rows.
For example, let’s say the query without LIMIT and OFFSET clauses returns 5 records. Now, if you specify LIMIT 5 OFFSET 3, postgresql will skip the 3 rows first and then applies the limit of 5. In this case, after skipping 3 rows, only 2 rows are left, therefore only 2 rows will be displayed.
Now let’s see some examples of LIMIT and OFFSET in postgresql.
Practical Examples: Using LIMIT and OFFSET
To demonstrate the LIMIT and OFFSET clauses, we will need a table with the sample data. I have already created a table as shown below.
Let’s now write the queries to demonstrate the LIMIT and OFFSET.
Example 1: Utilizing the LIMIT Clause
Let’s say we want to see the first three employees when all employee names are sorted in ascending order.
SELECT * FROM employees
ORDER BY first_name
LIMIT 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the query first sorts the employees by their first name and then applies the limit and returns the result.
Example 2: Implementing the OFFSET Clause
In the previous example, we have returned only the first three employees. But now, let’s say you want to skip the first employee when all employee names are sorted in ascending order and then return all other employees.
SELECT * FROM employees
ORDER BY first_name
OFFSET 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the query has skipped the first employee record and returned all other records.
Example 3: Combining LIMIT and OFFSET
Now let’s say you want the list of employees who are not managers but you want to skip the first employee when they are sorted in ascending order by their salaries. Also, you want to limit the number of employees to be shown to the 2.
SELECT * FROM employees
WHERE is_manager=false
ORDER BY salary
LIMIT 2 OFFSET 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, let’s understand the flow of execution.
There are three employees in our table who are not managers, therefore, the query will return 3 records without the LIMIT and OFFSET clauses. Note that, we have also sorted the records by their salaries. Therefore, the one having the lowest salary will be the first in the output.
After that, the OFFSET will skip the first employee, i.e., the employee having the lowest salary. Later, the LIMIT clause will apply the limit on the remaining rows. In our case, the number of rows is 2 anyway after the OFFSET, therefore LIMIT 2 will do its work and return these rows. Let’s check the output now.
As you can see, we have got the correct output.
Wrapping Up: Mastering LIMIT and OFFSET
We’ve delved deep into the intricacies of PostgreSQL’s LIMIT and OFFSET clauses. Remember, using these clauses without an ORDER BY can yield unpredictable results. So, always pair them with ORDER BY for consistent output. Ready to optimize your PostgreSQL queries even further?