If you’re getting an error in PostgreSQL that reads something like “function lead(numeric, numeric) does not exist“, it may be because your second argument is of the wrong data type.
The second argument to the lead() function is optional, but if it’s provided, it must be an integer.
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,
lead( productprice, 1.5 ) OVER ( ORDER BY productprice )
FROM products;Result:
ERROR: function lead(numeric, numeric) does not exist
LINE 5: lead( 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 lead(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,
lead( productprice, 1 ) 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
Here I simply changed 1.5 to 1 and I no longer got 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,
lead( productprice, 1, 0.00 ) 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 | 0.00
This time the last row is 0.00 instead of null.
Note 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,
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
The default value for the second argument is 1, and so we can omit it if we want to use 1 (i.e. we want lead() to return the value from the next row).