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.

</>
Copy
 SELECT *
	FROM tablename
	LIMIT N;

where 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.

</>
Copy
 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 3.

</>
Copy
 SELECT * 
	FROM students
	LIMIT 3;
PostgreSQL LIMIT Example

Example 2 – LIMIT Clause with OFFSET

Now, let us limit the number of rows in the result to 2 with offset of 2.

</>
Copy
 SELECT * 
	FROM students
	LIMIT 2 OFFSET 2;
PostgreSQL LIMIT OFFSET

Conclusion

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.