PostgreSQL is the relational database to store and manage structured data as millions of users are working on it daily. PostgreSQL provides the user with the SQL query language which can be used to access data from the database and get useful insights from it. It can also be used to manipulate the data from multiple relations or different rows from the same relation to get a better understanding of the data.

This guide will explain the LAG() function in PostgreSQL and the process of using it with examples.

How Does PostgreSQL LAG() Function Work

PostgreSQL uses Structured Query Language or SQL to fetch or manipulate data from the database to learn about the data. LAG() is a function used in the SQL language that can be used to fetch data from the previous row with respect to the provided offset value. It is a Windows function available that enables the user to compare the values of the current row and the previous row.

Syntax

Use the following syntax of the LAG() function in PostgreSQL as mentioned and explained below:

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

The above code suggests:

- The LAG keyword is used to specify the name of the function and then its argument list is mentioned in the small parentheses.
- The argument list of the LAG() function contains the “expression”, “offset”, and “default_value” where only the expression is mandatory, else are optional.
- The expression parameter specifies the column name or subquery which is evaluated against the previous row at a specific offset.
- The offset is used to specify the number of rows to return and its default value is 1 if not provided.
- The default_value is used to set the default value to return if the value is out of scope.
- After setting the LAG() function parameters, simply the ORDER BY function is used to get the resultant table in sorted order using a specific column or field.
- The PARTITION BY clause is an optional one that is used to divide the rows into parts on which the function is applied.

Example 1: How to Use LAG() Function?

To use the LAG() function in the PostgreSQL database, simply create a table or use the existing one(if any). This guide has used an already created “employee” table to illustrate the use of the LAG() function in Postgres:

SELECT * FROM employee;

Using the above command, the selected table has been displayed on the screen:

Apply the LAG() function on the above table using the following query:

SELECT name, salary, gender,
  LAG(salary, 1, -1) 
  OVER (ORDER BY salary) 
  AS Lag_1
 FROM employee;

The above query selects the columns from the employee table to apply the LAG() function with salary as its expression with 1 as offset, and -1 as its default_value. The above query uses the salary column to order the table and the resultant column will be named “Lag_1”:

The above screenshot displays that the Lag_1 column shows the salary of the previous row and the first cell contains the default_value.

Example 2: How to Use PARTITION BY Clause in LAG() Function?

The LAG() function can also be used with the PARTITION BY function as the following snippet contains the code:

SELECT name, salary, gender,
  LAG(salary, 1, -1) 
  OVER (PARTITION By gender 
   ORDER BY salary) 
  AS Lag_1
 FROM employee;

The above code adds the PARTITION BY clause in the previous example to divide the gender field into parts according to its values:

The above screenshot divides the resultant values in Female and Male from the gender column and then orders it using the salary column. The LAG() function resets its value when a new partition is reached.

That’s all about the LAG() function in PostgreSQL using examples.

Conclusion

To use the LAG() function in the PostgreSQL table, simply create a table in PostgreSQL to get the values of the previous rows from the table. The LAG() function contains three parameters including the expression, offset, and default_values, and retrieves the result accordingly. The LAG() function uses the ORDER BY clause to sort the table and the PARTITION BY clause is optional and can be used to divide the result set into parts. This post demonstrated the process of using the LAG() function in PostgreSQL.