In PostgreSQL the lag() function returns the value from a previous row, specified by the given offset.

The offset specifies how many rows prior to the current row to get the value from. For example, an offset of 1 gets the value from the previous row.

Syntax

The syntax goes like this:

lag ( value [, offset [, default ]] )

The value argument is required.

The offset argument is optional, and its default value is 1.

The optional default argument specifies what to use if there are no rows at the offset (for example, if we’re on the first row and we use an offset of 1 or more).

Example

Here’s an example to demonstrate:

\pset null 'null'
SELECT
    vendorid,
    productname,
    productprice,
    lag( productprice ) OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice |  lag  
----------+---------------------------------+--------------+-------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |  null
     1003 | Hammock                         |        10.00 |  9.99
     1001 | Long Weight (green)             |        11.99 | 10.00
     1004 | Tea Pot                         |        12.45 | 11.99
     1001 | Long Weight (blue)              |        14.75 | 12.45
     1001 | Left handed screwdriver         |        25.99 | 14.75
     1001 | Right handed screwdriver        |        25.99 | 25.99
     1002 | Sledge Hammer                   |        33.49 | 25.99
     1003 | Straw Dog Box                   |        55.99 | 33.49
     1003 | Chainsaw                        |       245.00 | 55.99
(10 rows)

We can see that the lag column returns the value from the previous row of the productprice column. That’s because I specified productprice as the first argument to lag() and I didn’t specify any other arguments. The other arguments are optional.

As mentioned, the default offset is 1, so if we don’t pass that argument we get the previous row.

On the first row, there’s no previous row, and so we get a null value.

For my first line, I decided to set the string for null values. Without doing this, my command line would return a blank field. This is purely optional, but it demonstrates that the lag() function returns null when there’s no row at the specified offset. We can use the lag() function’s optional default argument to change what’s returned in such cases (example later).

Specify an Offset

Here’s an example of specifying our own offset:

SELECT
    vendorid,
    productname,
    productprice,
    lag( productprice, 3 ) OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice |  lag  
----------+---------------------------------+--------------+-------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |  null
     1003 | Hammock                         |        10.00 |  null
     1001 | Long Weight (green)             |        11.99 |  null
     1004 | Tea Pot                         |        12.45 |  9.99
     1001 | Long Weight (blue)              |        14.75 | 10.00
     1001 | Left handed screwdriver         |        25.99 | 11.99
     1001 | Right handed screwdriver        |        25.99 | 12.45
     1002 | Sledge Hammer                   |        33.49 | 14.75
     1003 | Straw Dog Box                   |        55.99 | 25.99
     1003 | Chainsaw                        |       245.00 | 25.99
(10 rows)

This time the first three rows are null. That’s because I specified an offset of 3, which means that it goes back three rows to get the value. That obviously means that we won’t get a value until we’re on the fourth row.

Specify a Default Value

Here’s an example of specifying a default value to use for when there’s no value at the given offset:

SELECT
    vendorid,
    productname,
    productprice,
    lag( productprice, 3, 0.00 ) OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice |  lag  
----------+---------------------------------+--------------+-------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |  0.00
     1003 | Hammock                         |        10.00 |  0.00
     1001 | Long Weight (green)             |        11.99 |  0.00
     1004 | Tea Pot                         |        12.45 |  9.99
     1001 | Long Weight (blue)              |        14.75 | 10.00
     1001 | Left handed screwdriver         |        25.99 | 11.99
     1001 | Right handed screwdriver        |        25.99 | 12.45
     1002 | Sledge Hammer                   |        33.49 | 14.75
     1003 | Straw Dog Box                   |        55.99 | 25.99
     1003 | Chainsaw                        |       245.00 | 25.99
(10 rows)

Note that I provided a value that’s compatible with the productprice column. This is required.

Here’s what happens if I pass an incompatible value:

SELECT
    vendorid,
    productname,
    productprice,
    lag( productprice, 3, 'No Value' ) OVER ( ORDER BY productprice )
FROM products;

Result:

ERROR:  invalid input syntax for type numeric: "No Value"
LINE 5:     lag( productprice, 3, 'No Value' ) OVER ( ORDER BY produ...
                                  ^

An error occurs.