How To Use Limit And Offset In PostgreSQL

Faruk Erdem

1 year ago

In today’s article, we will examine how to use Limit and Offset clauses and their syntax structures in PostgreSQL.

Offset Clause:

Offset clause is used to skip some records before returning the result set of a query.

We can access the records in the first 20 lines and the next 30 lines with the help of the command below.

SELECT *
FROM personel
ORDER BY id   asc
OFFSET 20 ROWS 
FETCH NEXT 30 ROWS ONLY;

SELECT *

FROM personel

ORDER BY id asc

OFFSET 20 ROWS

FETCH NEXT 30 ROWS ONLY;

In the example above, the data is sorted from smallest to largest with the ORDER BY command, and we wanted to process the records after 20 records with the OFFSET 20 ROWS command.

With the FETCH NEXT 30 ROWS ONLY command, we say to perform the operation on the 30 records after 20.

Limit Clause:

Limit clause is used to restrict the number of rows returned by a query.

For example, if there are 100 records in our table, but we want only 3 records to be returned, we can use the limit clause.

SELECT
Column1,
Column2,
FROM
Table_Name
LIMIT (number of records requested)

SELECT

Column1,

Column2,

FROM

Table_Name

LIMIT (number of records requested)

In the example below, we retrieve 5 records from the Address table.