If you’re getting an error that reads “ERROR: argument of ntile must be greater than zero” in PostgreSQL, it’s probably because you’re calling the ntile() function with a non-positive integer.

This error occurs when we pass a non-positive integer to the ntile() function.

To fix this issue, be sure to pass a positive integer to the function.

Example of Error

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

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

Result:

ERROR:  argument of ntile must be greater than zero

We get the same error if we pass a negative value:

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

Result:

ERROR:  argument of ntile must be greater than zero

Either way the message is clear – the argument must be greater than zero.

Solution

The solution is easy: Provide a positive integer.

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

We can get similar, but different, errors when we pass the wrong argument type:

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

Result:

ERROR:  function ntile(numeric) does not exist

The same applies if we omit the argument altogether:

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

Result:

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.

Regardless, the solution is the same: make sure the argument is a positive integer that represents the number of buckets that the partition should have.