If you’re getting an error that reads something like “Could not convert string ‘…’ to INT64” when using the generate_subscripts() function in DuckDB, it appears that your second argument is a string, when it should be an integer.

DuckDB’s generate_subscripts() function accepts two arguments; the array as the first argument, and the dimension as the second argument. The second argument must be INT64 (or be able to be implicitly converted to that type). Passing the wrong data type as the second argument can cause the above error to occur.

To fix this issue, make sure that the second argument is compatible with INT64.

Example of Error

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

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

Result:

Conversion Error: Could not convert string 'Fish' to INT64
LINE 3:     'Fish'
            ^

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

It’s also possible to get a different error, depending on the data type we provide:

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 I passed a number, but it wasn’t an integer.

Solution

To fix this issue, we need to make sure the second argument is INT64 compatible (and that it corresponds to an actual dimension in the array):

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

Result:

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

In this time I passed 1, which is an integer that corresponds with a dimension in the array. Given the array only has one dimension, this is the only value that will work. Passing a value that doesn’t correspond to a dimension in the array will result in a different error.