--- title: "list_resize" slug: "dev-database-reference-sql-functions-lists-list-resize" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-list-resize" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # list_resize *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs) › Combine and change lists.* Returns a list cut, or padded, to a given number of elements. `array_resize` is the same function. ## Syntax ``` list_resize(list, size) list_resize(list, size, fill) array_resize(list, size) array_resize(list, size, fill) ``` | Argument | Type | Description | | --- | --- | --- | | `list` | A list of any type | The list to cut or pad. | | `size` | A number (or a string that holds one) | The number of elements in the result. Not negative. | | `fill` | A value of the list's element type | The value to pad with. Optional; NULL if you leave it out. | **Returns:** a list of the same type as `list`. **Aliases:** `array_resize`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_lists_list_resize`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four players, each with a list of scores (`scores`), a list of badges (`badges`) and a default score (`base`). Player 2 has a NULL score, player 3 has empty lists, and player 4 has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_lists_list_resize"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.players" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, scores BIGINT[], badges VARCHAR[], base BIGINT)") run(f"INSERT INTO {TABLE} VALUES " "(1, [1, 2, 3], ['gold', 'silver', 'bronze'], 2), " "(2, [3, NULL], ['silver'], -1), " "(3, [], [], 0), " "(4, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `list_resize` returns a list of exactly `size` elements: - **A longer list is cut** to its first `size` elements: `list_resize([1, 2, 3], 2)` is `[1, 2]`. `fill` isn't used. - **A shorter list is padded** at the end with `fill`, or with NULLs if there's no `fill`: `list_resize([1, 2, 3], 4)` is `[1, 2, 3, NULL]`, and `list_resize([1, 2, 3], 4, 42)` is `[1, 2, 3, 42]`. - **Only new positions are filled.** A NULL element already in the list stays NULL: `list_resize(scores, 3, 0)` on `[3, NULL]` gives `[3, NULL, 0]`. `fill` is converted to the list's element type: `list_resize([1], 3, 2.5)` is the `INTEGER[]` list `[1, 3, 3]`, with 2.5 rounded to 3. `size` is converted to a whole number in the same way, so a `size` of `2.5` gives three elements. On a list of lists, `fill` is a list: `list_resize([[1]], 2, [0])` is `[[1], [0]]`. ## NULLs and edge cases | Input | Result | | --- | --- | | `list_resize(NULL, 2)` or `list_resize(NULL, 2, 0)` | `NULL` | | `list_resize([1, 2], NULL)` | `[]` | | `list_resize([1], 3, NULL)` | `[1, NULL, NULL]` | | `list_resize([1, 2, 3], 0)` | `[]` | | `list_resize([], 3)` | `[NULL, NULL, NULL]` | | `list_resize([1, 2], 2.5)`, `list_resize([1, 2], '3')` | `[1, 2, NULL]` | | `list_resize([1], 3, 2.5)` | `[1, 3, 3]`, an `INTEGER[]` | | `list_resize(['a'], 3, 'x')` | `['a', 'x', 'x']` | | `len(list_resize([1], 100000))` | `100000` | | `list_resize([1, 2], -1)` | Error: `Type INT32 with value -1 can't be cast because the value is out of range for the destination type UINT64` | | `list_resize([1], 3, 'x')` | Error: `Could not convert string 'x' to INT32` | | `list_resize([], 3, 'x')` (an empty list with no type) | Error: `Unimplemented type for cast (VARCHAR -> "NULL")` | | A number, string, date, boolean or `BLOB` as `list` | Error: `No function matches the given name and argument types`, for example `'list_resize(INTEGER_LITERAL, INTEGER_LITERAL)'` | The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-lists-list-resize#set-up-the-examples): ``` run("SELECT list_resize(NULL, 2), list_resize(NULL, 2, 0), list_resize([1, 2], NULL), list_resize([1], 3, NULL), " "list_resize([1, 2, 3], 0), list_resize([], 3)") run("SELECT list_resize([1, 2], 2.5), list_resize([1, 2], '3'), list_resize([1], 3, 2.5), " "typeof(list_resize([1], 3, 2.5)), list_resize(['a'], 3, 'x'), len(list_resize([1], 100000))") run("SELECT list_resize([1, 2], -1)") run("SELECT list_resize([1], 3, 'x')") run("SELECT list_resize([], 3, 'x')") run("SELECT list_resize(123, 2)") ``` ``` [(None, None, [], [1, None, None], [], [None, None, None])] [([1, 2, None], [1, 2, None], [1, 3, 3], 'INTEGER[]', ['a', 'x', 'x'], 100000)] OSError: Type INT32 with value -1 can't be cast because the value is out of range for the destination type UINT64 OSError: Could not convert string 'x' to INT32 OSError: Unimplemented type for cast (VARCHAR -> "NULL") InternalError: Binder Error: No function matches the given name and argument types 'list_resize(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts. ``` ## Examples Keep each player's first two scores, and pad every list of scores to four with 0. Player 3's empty list is padded too; player 4's NULL list stays NULL: ``` run(f"SELECT id, list_resize(scores, 2), list_resize(scores, 4, 0) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, [1, 2], [1, 2, 3, 0]), (2, [3, None], [3, None, 0, 0]), (3, [None, None], [0, 0, 0, 0]), (4, None, None)] ``` Pad to three with a value from another column, and pad the badges with `'none'`. `array_resize` gives the same lists: ``` run(f"SELECT id, list_resize(scores, 3, base), array_resize(scores, 3, base), list_resize(badges, 3, 'none') " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, [1, 2, 3], [1, 2, 3], ['gold', 'silver', 'bronze']), (2, [3, None, -1], [3, None, -1], ['silver', 'none', 'none']), (3, [0, 0, 0], [0, 0, 0], ['none', 'none', 'none']), (4, None, None, None)] ``` Keep the players whose top two badges include `'silver'`: ``` run(f"SELECT id FROM {TABLE} WHERE list_contains(list_resize(badges, 2), 'silver') ORDER BY id LIMIT 10") ``` ``` [(1,), (2,)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). The scan reads a list column whole, and VAST DataBase doesn't evaluate list functions, so `list_resize()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the list column and the Query Engine computes `list_resize()` for each row it returns. - **In `WHERE`,** the scan sends the list for every row, and a `FILTER` step in the Query Engine computes the condition and keeps the rows that match. No rows are skipped in storage. The plan of the `WHERE` example shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE list_contains(list_resize(badges, 2), 'silver')") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ list_contains(list_resize │ │ (badges, 2), 'silver') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ badges │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Pair a list condition with a pushed-down condition.** In `base >= 0 AND list_contains(list_resize(badges, 2), 'silver')`, `base >= 0` is evaluated in VAST DataBase, so the list condition runs only on the rows that pass it. The `FILTER` step in the plan holds only the list condition: ``` plan(f"SELECT id FROM {TABLE} WHERE base >= 0 AND list_contains(list_resize(badges, 2), 'silver')") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ list_contains(list_resize │ │ (badges, 2), 'silver') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ badges │ │ id │ │ │ └───────────────────────────┘ ``` - **Pad when you write, if every reader pads.** If every query pads the lists to the same length, store them padded, so queries read them as they are. ## Limitations and errors - **`size` can't be negative.** `list_resize([1, 2], -1)` fails with `Type INT32 with value -1 can't be cast because the value is out of range for the destination type UINT64`. If `size` comes from a calculation, clamp it with `greatest(n, 0)`. - **`fill` must convert to the element type.** A string that isn't a number, used to pad an `INTEGER[]`, fails with `Could not convert string 'x' to INT32`. A fill that does convert is changed silently: `2.5` becomes `3` in an `INTEGER[]`. - **An empty list literal has no element type.** `list_resize([], 3, 'x')` fails with `Unimplemented type for cast (VARCHAR -> "NULL")`. Give the list a type when it's a literal; a list column always has one. - **A NULL `size` gives an empty list,** not NULL. ``` run("SELECT list_resize([1, 2], greatest(-1, 0)), list_resize([]::VARCHAR[], 3, 'x')") ``` ``` [([], ['x', 'x', 'x'])] ``` ## Similar functions | Function | Result for `[3, 1, NULL, 3, 10]` | | --- | --- | | `list_resize(l, 2)` | `[3, 1]` | | [list_slice](/develop/docs/dev-database-reference-sql-functions-lists-list-slice): `list_slice(l, 2, 3)`, elements 2 to 3 | `[1, NULL]` | | `l[2:3]`: the same slice | `[1, NULL]` | | [array_pop_back](/develop/docs/dev-database-reference-sql-functions-lists-array-pop-back): all but the last element | `[3, 1, NULL, 3]` | | [list_append](/develop/docs/dev-database-reference-sql-functions-lists-list-append): `list_append(l, 0)`, one more element | `[3, 1, NULL, 3, 10, 0]` | ``` run("SELECT list_resize(l, 2), list_slice(l, 2, 3), l[2:3], array_pop_back(l), list_append(l, 0) " "FROM (SELECT [3, 1, NULL, 3, 10] AS l)") ``` ``` [([3, 1], [1, None], [1, None], [3, 1, None, 3], [3, 1, None, 3, 10, 0])] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [list_slice](/develop/docs/dev-database-reference-sql-functions-lists-list-slice): take a range of elements. - [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the list functions. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.