PostgreSQL LIMIT Clause
When you make a SELECT query to the database, you get all the rows that satisfy the WHERE condition in the query. At times, these number of rows returned could be huge; and we may not use most of the results.
For example, in Google Search, you get only the first 10 results even though there are thousands or millions of results found for your query. Most of the times user is not interested in all of those results. If he needs any further results, he navigates through the pagination.
So, it is always advisable to limit the number of rows that we query a database. It reduces the load on server.
And in this tutorial, we are going to learn how to limit the number of rows when we query a database.
Syntax – LIMIT
LIMIT is the clause used in SQL to limit the number of rows in the query result.
The syntax to use LIMIT clause is shown below.
SELECT * FROM tablename LIMIT N;
N is an integer that specifies the number of rows the result set has to be limited to.
You can also specify a range, to fetch only those rows in that range of the result set.
SELECT * FROM tablename LIMIT N OFFSET i;
where N number of rows are fetched after offset i.
Example 1 – LIMIT Clause
Let us consider a table named students and run a SELECT query limiting the number of rows in the result to be
SELECT * FROM students LIMIT 3;
Example 2 – LIMIT Clause with OFFSET
Now, let us limit the number of rows in the result to
2 with offset of
SELECT * FROM students LIMIT 2 OFFSET 2;
In this PostgreSQL Tutorial, we have limited the number of rows in the result set to specific number. We have also seen examples using OFFSET with LIMIT clause.