Fix “function generate_subscripts(text[], integer, integer) does not exist” in PostgreSQL

If you’re getting a PostgreSQL error that reads something like “function generate_subscripts(text[], integer, integer) does not exist“, it’s probably because your third argument is of the wrong type when using the generate_subscripts() function.

The above error specifically implies that an integer was passed as the third argument, but it must be a boolean value.

The third argument of the generate_subscripts() function is optional, but if passed, it must be a boolean expression.

To fix this error, either pass a boolean value as the third argument, or eliminate the third argument altogether.

Example of Error

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

SELECT generate_subscripts(
    ARRAY['Cat','Dog','Rabbit'], 
    1, 
    1
    ) AS "Reverse Order";

Result:

ERROR:  function generate_subscripts(text[], integer, integer) does not exist
LINE 1: SELECT generate_subscripts(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I got the error because my third argument wasn’t a boolean expression. In this case it was an integer.

Solution 1

We can fix the problem by changing the third argument to a boolean value:

SELECT generate_subscripts(
    ARRAY['Cat','Dog','Rabbit'], 
    1, 
    true
    ) AS "Reverse Order";

Result:

 Reverse Order 
---------------
3
2
1

This time the function worked without error. That’s because I passed a boolean value as the third argument.

The function also accepts boolean string representations. So, if we really wanted to keep the 1 instead of true, we can surround it with quotes:

SELECT generate_subscripts(
    ARRAY['Cat','Dog','Rabbit'], 
    1, 
    '1'
    ) AS "Reverse Order";

Result:

 Reverse Order 
---------------
3
2
1

Or we could cast the 1 to a boolean:

SELECT generate_subscripts(
    ARRAY['Cat','Dog','Rabbit'], 
    1, 
    1::boolean
    ) AS "Reverse Order";

Result:

 Reverse Order 
---------------
3
2
1

And we could be even more explicit by doing both:

SELECT generate_subscripts(
    ARRAY['Cat','Dog','Rabbit'], 
    1, 
    '1'::boolean
    ) AS "Reverse Order";

Result:

 Reverse Order 
---------------
3
2
1

Other boolean string representations include true, yes, on, t, and y meaning we could replace '1' with 'true', 'yes', 'on', 't', or 'y' (i.e. enclosed in quotes).

Solution 2

The above solution assumes that we need to output the result in reverse order. If we don’t want to do that, then we can omit the third argument altogether:

SELECT generate_subscripts(
    ARRAY['Cat','Dog','Rabbit'], 
    1
    ) AS "Default Order";

Result:

 Default Order 
---------------
1
2
3

Omitting the third argument results in generate_subscripts() using the default value, which is false (i.e. reverse ordering is not true).