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.
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.
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
Let's use the LIMIT clause to fetch only the first 5 rows sorted by
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.
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.
SELECT * FROM Employee ORDER BY salary DESC LIMIT 3;