PostgreSQL Fetch and Limit

I recently stumbled on the Fetch clause in PostgreSQL which is functionally the same as the Limit clause, and this short article tries to explain these two clauses and make a distinction between them.

Limit Clause

This SQL clause is used when you want to limit the number of rows returned by your query. In an SQL query the limit clause comes after the order by clause which is used to order result in either ascending or descending order.

The syntax of the limit clause is:

LIMIT "number of row(s)" [OFFSET "number of row(s)"];

The “number of row(s)” is an integer value that is used to specify the number of rows to return from the query result. It takes an optional Offset clause that is used to specify the number of records to skip before returning “number of rows” result. If no offset is specified, no records will be skipped before limiting the result.

LIMIT 3
is same as
LIMIT 3 OFFSET 0

Sample usage in a select query:

SELECT column_list
FROM table
ORDER BY sort_method
LIMIT "number of row(s)" [OFFSET "number of row(s)"];

Fetch Clause

The fetch clause functions similarly to the limit clause. It is used to limit the number of rows returned by an sql query. Like the limit clause, it comes after the order by clause.

The syntax for the fetch clause is ;

FETCH first/next "number of row(s)" only;

The fetch clause can also take an optional Offset clause and if no offset is specified, no record will be skipped before fetching the result.

FETCH 5 ROWS ONLY
is same as
OFFSET 0
FETCH 5 ROWS ONLY

Sample usage in a select query:

SELECT column_name
FROM table
ORDER BY sorting_method
OFFSET “number of row(s)"
FETCH first “number of row(s)” or next "number of row(s)” only;

Differences between them

While fetch and limit have the same functionality, the fetch clause is an SQL standard while limit is not. Fetch was included in the SQL standard in 2008 which makes it the go to option if you want your query to be portable across several RDBMS. It is also worth mentioning that the limit clause is also quite popular as some RDBMS adopts it.

Thank you for reading.