In PostgreSQL, the user can store huge data in the form of tables and access it at any moment without any change to the data. With databases and tables, the user can manage big data easily and then fetch or access it using the SQL queries on the table. Getting useful insights from the tables enables the user to make better business or other decisions based on the data available for the business.

This guide will explain the working of the LEAD function in PostgreSQL.

How Does the LEAD Function Work in PostgreSQL?

The LEAD is a built-in Window function that can be used in the PostgreSQL database to get data from the following/subsequent row of the current row. It uses the offset to get data from the next rows of the table to compare the values of multiple rows within the table.

Syntax

The syntax of using the LEAD function in PostgreSQL is mentioned below:

LEAD(expression [,offset [,default_value]]) 
   OVER (
  ORDER BY sort_expression [ASC | DESC]
)

In the above code snippets:

- The LEAD keyword suggests the name of the function and is followed by the list of its arguments.
- The expression argument is used to get the name of the column on which the LEAD function is to be applied.
- The offset parameter of the LEAD function sets the rows to lead from the current row.
- The last argument is the default_value which is optional and used to set the values for the rows that do not have any following rows.
- The LEAD function also uses the ORDER BY clause to set the order of the table and the PARTITION BY clause is an optional clause to divide the table into smaller parts.

Example 1: How to Use LEAD Function?

This code is used to get the data from the employee table in PostgreSQL:

SELECT * FROM employee;

The following screenshot displays the data stored in the employee table to apply the LEAD function on it:

The following code uses the LEAD function to compare the rows from the employee table:

SELECT name,   gender, salary, 
  LEAD(salary, 2, -1) 
  OVER (ORDER BY salary) 
  AS Lead_1
 FROM employee;

The above code block selects the name, gender, and salary columns from the employee table to apply the LEAD function to it. The LEAD function is applied on the salary column with the offset of 2 and default_value -1. The LEAD function is ordered by the salary column and the resultant column will be named “Lead_1”:

The above screenshot displays the lead_1 function containing the values from 2 rows below their current row.

Example 2: PARTITION BY Clause With LEAD Function

The following code uses the LEAD function with PARTITION BY clause in the employee table:

SELECT name,   gender, salary,
  LEAD(salary, 2, -1) 
  OVER (PARTITION By   gender 
   ORDER BY salary) 
  AS Lead_1
 FROM employee;

The above code uses a gender column to partition the table to apply the LEAD function on the salary column:

That’s all about working the LEAD() function in PostgreSQL.

Conclusion

To use the LEAD function in PostgreSQL, simply create a table and insert data into it and use the LEAD function with its parameters. Use the column name of the table on which the LEAD function will be used and the offset will determine the comparison of the number of following/subsequent rows. This guide has explained the process of using the LEAD function and its working in PostgreSQL.