In PostgreSQL the lead() function returns the value from a subsequent row to the current row, specified by the given offset.

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

Syntax

The syntax goes like this:

lead ( 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 last row and we use an offset of 1 or more).

Example

Here’s an example to demonstrate:

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

Result:

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

We can see that the lead column returns the value from the next row of the productprice column. That’s because I specified productprice as the only argument to lead() and I used the default values for the other arguments (i.e. I didn’t specify any other arguments). The other arguments are optional.

The default offset is 1, and so if we omit that argument it’s assumed to be 1 and we get the next row.

We get null on the last row because there’s no subsequent row for which to take a value from.

By the way, the first line of code above sets the string for null values. Without this, my command line would return an empty string whenever there are null values. I only did that so that it was obvious when we got a null value.

We can also use the lead() function’s optional default argument to change what’s returned in such cases (example later).

Specify an Offset

Here’s an example of specifying an offset:

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

Result:

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

This time the last three rows are null. That’s because I specified an offset of 3, which means that it goes forward three rows to get the value. That means that we won’t get a value once we’re passed the fourth row from the end, due to the fact that there are no more rows for which to get a value from.

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,
    lead( productprice, 3, 0.00 ) OVER ( ORDER BY productprice )
FROM products;

Result:

 vendorid |           productname           | productprice |  lead  
----------+---------------------------------+--------------+--------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |  12.45
     1003 | Hammock                         |        10.00 |  14.75
     1001 | Long Weight (green)             |        11.99 |  25.99
     1004 | Tea Pot                         |        12.45 |  25.99
     1001 | Long Weight (blue)              |        14.75 |  33.49
     1001 | Left handed screwdriver         |        25.99 |  55.99
     1001 | Right handed screwdriver        |        25.99 | 245.00
     1002 | Sledge Hammer                   |        33.49 |   0.00
     1003 | Straw Dog Box                   |        55.99 |   0.00
     1003 | Chainsaw                        |       245.00 |   0.00
(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,
    lead( productprice, 3, 'No Value' ) OVER ( ORDER BY productprice )
FROM products;

Result:

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