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:

• 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:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  3	Jo	Paris	27	2800

• 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:

  EmpID	Name	City	Age	Salary
  3	Jo	Paris	27	2800
  4	Kim	Amsterdam	30	3100
  5	Ramesh	New Delhi	28	3000

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:

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: