How to Use LAG Function in PostgreSQL: Syntax and Examples

In PostgreSQL, use the LAG() window function to access data from a previous row in the same result set without a self-join. It helps compare current row values with prior rows by specifying the column, offset, and an optional default value.

Syntax

The LAG() function syntax is:

• LAG(expression [, offset [, default_value]]) OVER (PARTITION BY partition_column ORDER BY order_column)

Explanation:

• expression: The column or value you want to look back on.
• offset: How many rows back to look (default is 1).
• default_value: Value to return if there is no previous row (optional).
• PARTITION BY: Divides rows into groups to apply the function separately.
• ORDER BY: Defines the order of rows to determine the previous row.

sql

LAG(expression [, offset [, default_value]]) OVER (PARTITION BY partition_column ORDER BY order_column)

Example

This example shows how to use LAG() to compare each employee's salary with the previous employee's salary ordered by their ID.

sql

CREATE TABLE employees (id INT, name TEXT, salary INT);

INSERT INTO employees VALUES
(1, 'Alice', 5000),
(2, 'Bob', 6000),
(3, 'Charlie', 5500),
(4, 'Diana', 7000);

SELECT
  id,
  name,
  salary,
  LAG(salary, 1, 0) OVER (ORDER BY id) AS previous_salary
FROM employees
ORDER BY id;

Output

 id |  name   | salary | previous_salary 
----+---------+--------+-----------------
  1 | Alice   |   5000 |               0
  2 | Bob     |   6000 |            5000
  3 | Charlie |   5500 |            6000
  4 | Diana   |   7000 |            5500

Common Pitfalls

Common mistakes when using LAG() include:

• Not specifying ORDER BY inside OVER(), which leads to unpredictable previous rows.
• Forgetting to handle NULLs when there is no previous row, which can cause unexpected NULL results.
• Using LAG() without PARTITION BY when you want to reset the lag for groups.

Example of a wrong and right usage:

sql

-- Wrong: Missing ORDER BY, result is unpredictable
SELECT id, name, salary, LAG(salary) OVER () AS prev_salary FROM employees;

-- Right: Specify ORDER BY to get correct previous row
SELECT id, name, salary, LAG(salary) OVER (ORDER BY id) AS prev_salary FROM employees;

Quick Reference

Parameter	Description	Default
expression	Column or value to look back on	Required
offset	Number of rows back to look	1
default_value	Value if no previous row exists	NULL
PARTITION BY	Groups rows for separate lag calculation	No partition (all rows)
ORDER BY	Defines row order for lag calculation	Required

Key Takeaways

Use LAG() with ORDER BY to access previous row values in a query result.

Specify PARTITION BY to reset lag calculation for groups of rows.

Provide a default value to avoid NULL when no previous row exists.

Without ORDER BY, LAG() results are unpredictable.

LAG() helps compare current row data with prior rows efficiently.