How to Limit Rows in PostgreSQL

1. Use the LIMIT Clause to Limit Rows in PostgreSQL
2. Use the FETCH FIRST Clause to Limit Rows in PostgreSQL

In PostgreSQL, we can utilize the SELECT statement to list down rows from a table. However, if we want to list only selective rows, we must use various clauses accordingly.

One clause used with the SELECT statement to display selective rows from a table is the LIMIT clause. The LIMIT clause can restrict the number of rows listed in the resultant data.

This article will describe the LIMIT keyword and its uses in PostgreSQL.

Use the LIMIT Clause to Limit Rows in PostgreSQL

The syntax of the LIMIT clause is shown below:

LIMIT { count }

Here, the count field refers to the number of rows that will be counted and displayed. The ALL keyword can be used in place of count to display all the rows returned by the query, having the same effect as no limit.

Let us understand how the LIMIT clause is used through a sample database having the following table:

create table dummy
(
num int not null,
constraint PK primary key (num)
);

Now, let us fill this sample table with some values:

insert into dummy values (150), (120), (330), (240), (150), (60), (270), (110), (400), (350);

If we use a simple SELECT * from dummy statement to list the rows of this table, we will get the following result:

What if we want to display only the first five rows? We can easily use the LIMIT clause for that.

It is an optional functionality of the SELECT statement, which can be used in the following way:

SELECT * from dummy
LIMIT 5;

This will list down only the first five rows of the table:

Note: If the value of rows specified in the LIMIT clause is greater than the rows present in the table, all table rows are displayed.

However, the LIMIT clause is not very sensible to use if the rows are not sorted, which means that their order is unpredictable, and the LIMIT query might display varying results. We can counter this by using the LIMIT clause with the ORDER BY clause in the SELECT query.

The ORDER BY command lets you sort the data based on one or multiple attributes from the table. Using that set of attributes, you can also sort in ascending or descending order.

Let us look at the effect of using the LIMIT clause with ORDER BY on our dummy table. The code will be as follows:

SELECT * from dummy
ORDER BY num
LIMIT 5;

This will give us the following result:

We can see the query has returned the five smallest values of num because the ORDER BY clause sorted the data in ascending order by default. Therefore, we can use the LIMIT and ORDER BY clauses to fetch the highest or lowest specified number of values.

What if we do not wish to display the rows starting from the first one? The LIMIT clause grants us flexibility for doing this as well.

The syntax then becomes like this:

LIMIT { count }
OFFSET { start }

The value in place of start specifies the number of rows that will be skipped before the specified number of rows is displayed. This means that the start number of rows is skipped, and the next count number of rows is displayed as output.

Note: If the value of start specified in the OFFSET clause is greater than the rows present in the table, no table rows are displayed.

Let us demonstrate the use of OFFSET by running the following query on the dummy table:

SELECT * from dummy
ORDER BY num
LIMIT 5
OFFSET 2;

This code will begin from the third row, skipping the first two as specified, and then display the next five as follows:

Note: If the value in place of count is set as NULL, it will have the same effect as no limit and display all resultant rows. If the value in place of start is set as NULL, it will have the same effect as OFFSET 0 and will start displaying from the first row.

PostgreSQL offers an equivalent to the LIMIT clause, which we can use to output only the required number of rows from a table. This is explained below.

Use the FETCH FIRST Clause to Limit Rows in PostgreSQL

An alternative to the LIMIT clause is the FETCH FIRST clause, which has the following syntax:

FETCH FIRST { count } ROWS ONLY;

Here again, count is replaced by the number of rows we wish to be displayed. Let us look at how the FETCH FIRST clause is used in code by using the same dummy table:

SELECT * from dummy
ORDER BY num
FETCH FIRST 5 ROWS ONLY;

This will have the same effect as writing LIMIT 5 and will display the following resultant rows:

However, if we do not want to display the rows starting from the first one, we can use the OFFSET clause with FETCH FIRST. This is written in the following way:

OFFSET { start }
FETCH FIRST { count } ROWS ONLY;

Let us use this in our dummy table in the form of this query:

SELECT * from dummy
ORDER BY num
OFFSET 2
FETCH FIRST 5 ROWS ONLY;

This will fetch and print the following rows after skipping the first two:

This sums up the different ways the LIMIT clause is used in PostgreSQL to restrict the number of rows displayed resulting from a SELECT query. We hope you learned the usage of the LIMIT and FETCH FIRST commands as alternatives to each other.