If you’re getting an error that reads “ERROR:  invalid input syntax for type integer” in PostgreSQL, it’s probably because you’re calling the ntile() function with an argument of the wrong data type.

This error can occur when we pass the wrong type to the ntile() function. We can get a different error depending on the type, but this error can occur when we pass a string.

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( 'four' ) OVER ( )
FROM products;

Result:

ERROR:  invalid input syntax for type integer: "four"

Here, I passed a string to the ntile() function and this caused it to return an error.

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 Numeric Type

We can get different errors, depending on the exact type/value that we’re trying to pass.

Here’s what happens when the argument is of the wrong numeric type:

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

Result:

ERROR:  function ntile(numeric) does not exist

Here’s a non-positive integer:

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

Result:

ERROR:  argument of ntile must be greater than zero

And here’s what happens when 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.