If you’re getting an error that reads “function ntile() does not exist” in PostgreSQL, it could be that you’re calling the ntile() function without any arguments.

This error message could be a bit misleading. The ntile() function does in fact exist (if you’re using PostgreSQL 8.4 or later), but the error occurs when we don’t provide an argument, or if we provide an argument of the wrong type.

That said, the error message is technically correct. By saying that the ntile() function doesn’t exist is saying that it doesn’t exist without any arguments, which is correct. The syntax is ntile( num_buckets integer ) and so yeah, ntile() doesn’t quite cut it.

Either way, to fix this issue we need to pass an integer as an argument. This is a required argument that specifies how many buckets to separate the partition into.

Example of Error

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

SELECT 
    vendorid,
    productname,
    productprice,
    ntile( ) OVER ( )
FROM products;

Result:

function ntile() does not exist

That’s the error message I get when I run the code in my GUI client (Azure Data Studio). When I run the code at the command line, the message is a bit more helpful:

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

It specifies that “No function matches the given name and argument types.“.

Solution

The solution is easy: Provide a positive integer as an argument.

Here’s an example:

SELECT 
    vendorid,
    productname,
    productprice,
    ntile( 4 ) OVER ( )
FROM products;

Result:

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

This time the function worked as expected.

Passing the Wrong Type

As mentioned, the argument needs to be an integer.

Here’s what happens when we pass a different numeric type:

SELECT 
    vendorid,
    productname,
    productprice,
    ntile( 1.2 ) OVER ( )
FROM products;

Result:

ERROR:  function ntile(numeric) does not exist

We can get various other error messages, depending on the type that we pass.

Also, the integer needs to be 1 or greater. Here’s what happens when we pass zero:

SELECT 
    vendorid,
    productname,
    productprice,
    ntile( 0 ) OVER ( )
FROM products;

Result:

ERROR:  argument of ntile must be greater than zero