Using LIST_RESIZE() to Resize a List in DuckDB

by

DuckDB provides us with a list_resize() function for those occasions where we need to resize a list. It also offers an alias called array_resize() that does exactly the same thing. When we resize a list using these functions, we specify how big we want the resulting list to be, and we can also specify a value to use for any extra elements that are added to the list.

Syntax

The syntax goes like this:

list_resize(list, size[, value])
  • list: The list we want to resize.
  • size: The size we want the resulting list to be.
  • value: An optional value to use for any elements that are added to the list as a result of the resize operation.

As mentioned, there’s also an array_resize() function, which is an alias for list_resize(). So we can use the following syntax to achieve the same result:

array_resize(list, size[, value])

Example

Here’s a simple example to demonstrate:

SELECT list_resize(['Bark', 'Pant'], 5);

Result:

[Bark, Pant, NULL, NULL, NULL]

Here, the original list contained two items, and we increased its size to five. I didn’t specify a value for each extra list element and so NULL was used.

Padding the Result

We have the option of specifying a value to use for the extra list items:

SELECT list_resize(['Bark', 'Pant'], 5, 'Wag');

Result:

[Bark, Pant, Wag, Wag, Wag]

Here, the original list contained two items, and we increased its size to five. We specified that the string Wag should be used for each additional item. Therefore, Wag was used instead of NULL.

Explicitly Padding with NULL

We can explicitly specify NULL for the extra items if we want:

SELECT list_resize(['Bark', 'Pant'], 5, NULL);

Result:

[Bark, Pant, NULL, NULL, NULL]

This is the same result we got when we didn’t specify a third argument.

Padding with Numbers

We can use numbers for the extra elements:

SELECT list_resize(['Bark', 'Pant'], 5, 0);

Result:

[Bark, Pant, 0, 0, 0]

Shortening the List

We don’t have to make the list bigger. We can also make it smaller:

SELECT list_resize(['Bark', 'Pant', 'Wag', 'Bite'], 3);

Result:

[Bark, Pant, Wag]

In this case there’s no need to specify a third argument, as we’re cutting the list short instead of extending it. But we won’t get an error if we do include a third argument:

SELECT list_resize(['Bark', 'Pant', 'Wag', 'Bite'], 3, 'Run');

Result:

[Bark, Pant, Wag]

Emptying the List

Passing zero as the second argument results in an empty list:

SELECT list_resize(['Bark', 'Pant', 'Wag', 'Bite'], 0);

Result:

[]

Specifying a Negative List Size

Passing a negative value as the second argument results in an error:

SELECT list_resize(['Bark', 'Pant', 'Wag', 'Bite'], -1);

Result:

Conversion Error:
Type INT32 with value -1 can't be cast because the value is out of range for the destination type UINT64

LINE 1: SELECT list_resize(['Bark', 'Pant', 'Wag', 'Bite'], -1);
                                                            ^

The array_resize() Function

The array_resize() function is an alias for list_resize(), so we can use either function to achieve the same result. For example:

SELECT array_resize(['Bark', 'Pant'], 5, 'Wag');

Result:

[Bark, Pant, Wag, Wag, Wag]