PostgreSQL: LIMIT and OFFSET

PostgresQL support LIMIT and OFFSET clauses in the SELECT query. The LIMIT clause is used to restrict the number of rows returned by a query. The OFFSET clause is used to skip the number of rows in the resultset of a query.

Syntax: LIMIT & OFFSET

SELECT <column_names>
FROM <table_name>
LIMIT <row_count> OFFSET <rows_to_skip>;

In the above syntax, row_count shows the number of rows to be returned from the query. If row_count is zero, the query will return an empty resultset. If row_count is null, the query will behave as if there is no LIMIT clause specified, meaning the query will return all rows from the resultset.

The OFFSET clause is used to skip the number of rows before returning the resultset. It can be used alone or can be used with the LIMIT clause. After the OFFSET keyword, specify rows_to_skip, so the query will skip the number of rows specified in rows_to_skip before returning the resultset. If the rows_to_skip is specified as zero, the query will behave as if there is no OFFSET clause specified.

Note: A table can have data stored in unspecified order, it is better to use LIMIT and OFFSET clauses along with ORDER BY clause to control the row order. If you do not use the ORDER BY clause, then LIMIT and OFFSET may return the result set in unspecified order of rows.

Let's use the following Employee table.

Let's use the LIMIT clause to fetch only the first 5 rows sorted by emp_id.

Example: LIMIT Clause

SELECT * FROM Employee
ORDER BY emp_id
LIMIT 5;

The above query will return the following result.

Now let's fetch 4 rows, after skipping the first 3 rows order by emp_id by using LIMIT and OFFSET clauses in the SELECT query.

Example: LIMIT and OFFSET

SELECT * FROM Employee
ORDER BY emp_id
LIMIT 4 OFFSET 3;

Normally, the LIMIT and OFFSET rows are used to get the first or last N rows. For example, to get the top 3 employees with the highest salary, sort the salary by descending order and use the LIMIT clause to get the first 3 employees.

Example: Get Top N Values

SELECT * FROM Employee
ORDER BY salary DESC
LIMIT 3;