PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References
PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References

PostgreSQL LIMIT Keyword



The PostgreSQL LIMIT keyword is used to fetch specified number of records from a table. This is useful when the table contains thousands of records and returning a large dataset can impact performance.

Syntax

The syntax for using LIMIT keyword in PostgreSQL is given below:

SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT number;

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • To fetch top 3 records from the Employee table, the query is:

    SELECT * FROM Employee LIMIT 3;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
  • To fetch top 3 records where the Age of the employee is greater than 25, the query will be:

    SELECT * FROM Employee 
    WHERE Age > 25
    LIMIT 3;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    3JoParis272800
    4KimAmsterdam303100
    5RameshNew Delhi283000

Using OFFSET with LIMIT query

The OFFSET value is most often used with the LIMIT query. The OFFSET value allows the user to specify which row to start from to retrieve the data.

Syntax

The syntax for using OFFSET keyword in PostGreSQL is given below:

SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT fetch_row_count OFFSET offset_row_count;

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

To fetch top 2-5 records from the Employee table, the query is:

SELECT * FROM Employee LIMIT 4 OFFSET 1;

This will produce the result as shown below:

EmpIDNameCityAgeSalary
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000

❮ PostgreSQL Keywords