If you’re getting an error that reads “window function nth_value requires an OVER clause” in PostgreSQL, it’s because you’re calling the nth_value() function without an OVER clause.

PostgreSQL requires that you include an OVER clause any time you call a built in nonaggregate window function such as nth_value().

To fix this issue, add an OVER clause to your window function.

Example of Error

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

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

Result:

ERROR:  window function nth_value requires an OVER clause
LINE 5:     nth_value( productprice, 3 )
            ^

I got the error because I didn’t provide an OVER clause.

Solution

As mentioned, the solution is to provide an OVER clause.

For example:

SELECT
    vendorid,
    productname,
    productprice,
    nth_value( productprice, 3 ) OVER ( 
        ORDER BY productprice
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )
FROM products;

Result:

 vendorid |           productname           | productprice | nth_value 
----------+---------------------------------+--------------+-----------
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |     11.99
     1003 | Hammock                         |        10.00 |     11.99
     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

In this case my OVER clause specifies the order, as well as the window frame. See my introduction to the SQL OVER clause for more information on how it works.