PostgreSQL provides an optional clause for the SELECT query named LIMIT. The LIMIT clause limits the data/record returned by the SELECT query. An optional clause named OFFSET can be used with the LIMIT clause to omit/skip some rows. All in all, we can say that the LIMIT clause and OFFSET clause allow us to retrieve only a subset of data returned/generated by the SELECT query.

This write-up is going to present a detailed understanding of the LIMIT clause with the help of some examples. So, let’s start!

How to Use LIMIT Clause in PostgreSQL

A LIMIT clause restricts/limits the number of rows retrieved by the SELECT statement. Let’s have a look at the below syntax to get a basic understanding of the LIMIT clause:

SELECT col_1, col_2, col_N
FROM tab_name
LIMIT number_of_rows;

Let’s understand how the LIMIT clause works in PostgreSQL:

• col_1, col_2, col_3, …, col_N are the columns to be selected.
• tab_name is a table whose columns will be selected.
• “LIMIT number_of_rows” represents the number of rows to be selected from the result set returned by the SELECT query.

Example 1: Using LIMIT Clause in Postgres SELECT Statement

Suppose we already have a table named “bike_details”. Let’s run the SELECT query to fetch the table details:

SELECT * FROM bike_details;

The output shows that there are ten rows in the bike_details table:

Now let’s execute the SELECT statement with the LIMIT clause to fetch only five rows of the bike_details table:

SELECT * FROM bike_details
LIMIT 5;

In the first step, we observed that there are ten rows in the bike_details table. However, the LIMIT clause allows us to fetch the desired number of rows from the selected table.

Example 2: Fetching the First Three Rows of the bike_color and bike_number Columns

Using the LIMIT clause, we can fetch any number of rows from any specific table column(s):

SELECT bike_color, bike_number
FROM bike_details
LIMIT 3;

This time, the LIMIT clause retrieves the first three rows of the bike_color and bike_number columns.

Example 3: Fetching the Last Three Rows of a Table in PostgreSQL

In PostgreSQL, the ORDER BY clause is used to specify an order, such as ascending or descending. Let’s use the LIMIT and ORDER BY clauses to fetch the last three rows (for bike_id) of the bike_details table:

SELECT * FROM bike_details
ORDER BY bike_id DESC
LIMIT 3;

This is how you can fetch the record of any specific table from the bottom.

How to Use LIMIT Clause With the OFFSET Clause in PostgreSQL

In PostgreSQL, an optional clause named OFFSET can be used with the collaboration of the LIMIT clause to skip some rows of a table.

Example: How Does OFFSET Clause Work in PostgreSQL

Let’s consider the following snippet to understand the workings of the LIMIT clause:

SELECT * FROM bike_details
LIMIT 3 OFFSET 2;

The above snippet will perform the following functionalities:

• The SELECT statement fetches all the columns of the bike_details table.
• The LIMIT clause retrieves only three rows.
• The OFFSET skips the first two rows of the bike_details table.

The output clarifies that the OFFSET clause skipped the first two rows and the LIMIT clause retrieved the next three rows of the bike_details table.

How to Use LIMIT With WHERE Clause in Postgres

You can use LIMIT with the WHERE clause to fetch the limited set of table rows based on certain criteria(as specified in WHERE). We will implement this concept on an "employee_attendence" table whose details are shown below:

The table keeps a total of seven records (from emp_id 1-7). Let's execute the SELECT query with LIMIT and WHERE Clauses to fetch only three employees having IDs less than or equal to 5:

SELECT * FROM employee_attendence
WHERE emp_id <=5
LIMIT 3;

How to Get a Limited Number of Random Records in Postgres

Normally, invoking the LIMIT clause retrieves a limited number of table rows from the top or bottom(depending on the specified ORDER). However, you can also fetch a limited number of random rows from a table using the LIMIT clause with the RANDOM() method:

SELECT * FROM employee_attendence
ORDER BY RANDOM()
LIMIT 3;

Each time you execute the above query, you will get a different result:

That was all the necessary details related to the Postgres LIMIT Clause.

Conclusion

In PostgreSQL, an optional clause named LIMIT is used to limit the data/record returned by the SELECT query. The OFFSET clause can be used optionally with the LIMIT clause to omit/skip some rows of the selected table. This post has explained the several use cases of the LIMIT clause with the help of different examples.