There are at least a couple of reasons you might get an error that reads “function nth_value(numeric) does not exist” in PostgreSQL.

It could be that you’re calling the nth_value() function without the correct number of arguments. It could also be that you’re passing the wrong argument type.

To fix this issue, be sure to pass the correct number of arguments, with the correct type.

Despite the error informing us that the function doesn’t exist, the nth_value() function has been included since PostgreSQL 8.4 when window functions were first introduced in Postgres. That said, if we look closely, the error actually tells us that nth_value(numeric) doesn’t exist. In other words, it doesn’t exist with a single numeric argument. You might get variations of this error, such as function nth_value(integer) does not exist, or function nth_value(numeric, numeric) does not exist. Either way, they all highlight a problem with the arguments.

Reason 1: Wrong Number of Arguments

As mentioned, one reason for the error is that we call the function without the correct number of arguments:

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

Result:

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

In this case I passed the column name for the first argument, but I forgot to pass the second argument (which specifies which row to get the value from).

We get the same error if we do it the other way around: pass the row number but forget the column name:

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

Result:

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

Reason 2: Wrong Argument Type

We can also get the error if we pass the correct number of arguments, but the second argument is of the wrong type:

SELECT
    vendorid,
    productname,
    productprice,
    nth_value( productprice, 3.5 ) OVER ( ORDER BY productprice )
FROM products;

Result:

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

The second argument must be an integer, and so this code example produces an error.

Solution

Regardless of the reasons for the error, the solution is to pass the correct number of arguments, and ensure that the second argument is an integer:

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

Result:

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

This time it worked as expected, without error. That’s because the first argument is the name of the column and the second argument is an integer that species the row number for which to retrieve the value from.