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 work 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: How to Use LIMIT Clause in Postgres SELECT Statement?

The below-given steps will help you to understand the concept of the LIMIT clause in a better way.

Step 1: Fetch the Table Using 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.

Step 2: Use LIMIT Clause to Fetch Limited Rows

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: How to Fetch 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:

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: How to Fetch 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 working of the LIMIT clause:

SELECT * FROM bike_details
LIMIT 3 OFFSET 2;

The above snippet will perform the following functionalities:

- The SELECT statement will fetch all the columns of the bike_details table.

- The LIMIT clause will retrieve only three rows.

- The OFFSET will skip 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.

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 explained the working of the LIMIT clause with the help of several examples.