If you’re getting an error that reads something like “No function matches the given name and argument types” when using the generate_subscripts() function in DuckDB, it could be that your first argument is not an array. Or it could be that your second argument is not compatible with the INT64 type.

DuckDB’s generate_subscripts() function accepts two arguments; the array as the first argument, and the dimension as the second argument. Passing a non-array as the first argument will cause the above error to occur. Passing a non-integer can also cause the issue.

To fix this issue, make sure that the first argument is a valid array and the second argument is an integer (specifically, an INT64).

Example of Error

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

SELECT generate_subscripts(
    'Sun', 
    1
    );

Result:

Binder Error: No function matches the given name and argument types 'array_length(STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	array_length(ANY[]) -> BIGINT
	array_length(ANY[], BIGINT) -> BIGINT

LINE 2:     'Sun', 
             ^

In this case, my first argument was a string instead of an array. This resulted in the error.

I get the same error if I pass another non-array type:

SELECT generate_subscripts(
    DATE '2020-12-01', 
    1
    );

Result:

Binder Error: No function matches the given name and argument types 'array_length(DATE, INTEGER_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	array_length(ANY[]) -> BIGINT
	array_length(ANY[], BIGINT) -> BIGINT

LINE 2:     DATE '2020-12-01', 
             ^

The error can also occur when the second argument is of the wrong argument type:

SELECT generate_subscripts(
    array_value('Sun', 'Sky', 'Sea'), 
    1.1
    );

Result:

Binder Error: No function matches the given name and argument types 'array_length(VARCHAR[3], DECIMAL(2,1))'. You might need to add explicit type casts.
	Candidate functions:
	array_length(ANY[]) -> BIGINT
	array_length(ANY[], BIGINT) -> BIGINT

LINE 2:     array_value('Sun', 'Sky', 'Sea'), 
             ^

In this case, the first argument was fine. The second argument caused the error.

Solution

To fix this issue, we need to pass an array as the first argument and an integer (INT64) as the second.

Example:

SELECT generate_subscripts(
    array_value('Sun', 'Sky', 'Sea'), 
    1
    );

Result:

+----------------------------------------------------------+
| generate_subscripts(array_value('Sun', 'Sky', 'Sea'), 1) |
+----------------------------------------------------------+
| 1                                                        |
| 2                                                        |
| 3                                                        |
+----------------------------------------------------------+

Problem solved.