If you’re getting an error in PostgreSQL that reads something like “function lag(numeric, numeric) does not exist“, it could be because your second argument is the wrong data type.

The second argument to the lag() function is optional, but if it’s provided, it must be an integer.

So to fix this issue, make sure the second argument is an integer. Alternatively, you can omit the second argument altogether if you’re happy to use the default value of 1.

Example of Error

Here’s an example of code that produces the error:

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

Result:

ERROR:  function lag(numeric, numeric) does not exist
LINE 5:     lag( productprice, 1.5 ) OVER ( ORDER BY productprice )
            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The error tells us that the function doesn’t exist, even though it does (at least from PostgreSQL 8.4). But if we look closely, it actually tells us that the function doesn’t exist with arguments of that type. Therefore, we can’t use the function in the form lag(numeric, numeric).

In my case, the second argument must be an integer but I’m passing a numeric type.

Solution 1

One option for fixing this issue is to ensure that the second argument is an integer:

SELECT
    vendorid,
    productname,
    productprice,
    lag( productprice, 1 ) 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

Here I simply changed 1.5 to 1 and we no longer get the error.

This solution is useful if you’re also providing the optional third argument (which specifies what to return if there’s a null value).

Here’s an example of using a third argument:

SELECT
    vendorid,
    productname,
    productprice,
    lag( productprice, 1, 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 |  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

Although bear in mind that the third argument (if provided) must be of a type that’s compatible with the first argument.

Solution 2

Another option is to remove the second (and any third) argument altogether:

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

The default value for the second argument is 1, and so we can omit it if we want that value (i.e. we want lag() to return the value from the previous row).