--- title: "list_concat" slug: "dev-database-reference-sql-functions-lists-list-concat" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-list-concat" --- > ## 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_concat *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 the elements of one list followed by the elements of another. `list_cat`, `array_concat` and `array_cat` are the same function, and so are `l1 + l2` and `add(l1, l2)`. `l1 || l2` joins lists too, but returns NULL when either list is NULL. ## Syntax ``` list_concat(list1, list2) list_cat(list1, list2) array_concat(list1, list2) array_cat(list1, list2) list1 + list2 add(list1, list2) list1 || list2 ``` | Argument | Type | Description | | --- | --- | --- | | `list1` | a list (`ANY[]`) | The elements that come first. | | `list2` | a list with an element type compatible with `list1` | The elements that come after. | **Returns:** a list of the two lists' common element type. **Aliases:** `list_cat`, `array_concat`, `array_cat`, `+`, `add`. `||` differs on NULLs. ## 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_concat`. 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 shopping carts: the items, the extras, the item prices and the fees. The second cart has no extras or fees (NULL), the third has empty lists and the last row 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_concat"' 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}.carts" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, items VARCHAR[], extras VARCHAR[], prices BIGINT[], fees DOUBLE[])") run(f"INSERT INTO {TABLE} VALUES " "(1, ['pen', 'ink'], ['bag'], [3, 4], [0.5]), " "(2, ['cup'], NULL, [2], NULL), " "(3, [], [], [], []), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `list_concat(l1, l2)` returns the elements of `l1` followed by those of `l2`: `list_concat([1, 2], [3])` is `[1, 2, 3]`. Duplicates are kept, NULL elements are kept, and nothing is sorted. - **One element type.** The element types are combined: `list_concat([1], [2.5])` is a `DECIMAL(11,1)[]` list, and a `BIGINT[]` list joined with a `DOUBLE[]` list is a `DOUBLE[]` list. Lists that can't be combined, such as `INTEGER[]` and `VARCHAR[]`, fail. - **Two lists at a time.** For three or more, chain `||` or `+` (`[1] || [2] || [3]`) or nest the calls (`list_concat(list_concat(a, b), c)`). - **A NULL list counts as empty** for `list_concat`, its aliases, `+` and `add`: `list_concat(['cup'], NULL)` is `['cup']`. - **`||` returns NULL** if either list is NULL: `['cup'] || NULL` is NULL. Use it when a missing list should make the result missing. ## NULLs and edge cases | Input | Result | | --- | --- | | `list_concat(NULL, [9])` | `[9]` | | `list_concat([1], NULL)` | `[1]` | | `list_concat(NULL::BIGINT[], NULL::BIGINT[])` | `[]` | | `list_concat(NULL, NULL)`, both untyped | `''` (an empty string) | | `list_concat([], [])` | `[]` | | `list_concat([NULL], [NULL])` | `[NULL, NULL]` | | `list_concat([1], ['a'])` | Error: `Cannot concatenate lists of types INTEGER[] and VARCHAR[] - an explicit cast is required` | | `list_concat(123, [9])`: not a list | Error: `No function matches the given name and argument types` | `+` and `add` give the same results on NULLs. The `||` operator returns `NULL` for `NULL || [9]`, `[1] || NULL` and `NULL::BIGINT[] || NULL::BIGINT[]`, and `[1] || 2` (a list and an element) fails with `Cannot concatenate types INTEGER[] and INTEGER - an explicit cast is required`. The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-lists-list-concat#set-up-the-examples): ``` run("SELECT list_concat(NULL, [9]), NULL + [9], add(NULL, [9]), NULL || [9]") run("SELECT list_concat([1], NULL), [1] + NULL, [1] || NULL") run("SELECT list_concat(NULL::BIGINT[], NULL::BIGINT[]), NULL::BIGINT[] || NULL::BIGINT[], list_concat(NULL, NULL)") run("SELECT list_concat([], []), list_concat([NULL], [NULL])") run("SELECT list_concat([1], ['a'])") run("SELECT list_concat(123, [9])") run("SELECT [1] || 2") ``` ``` [([9], [9], [9], None)] [([1], [1], None)] [([], None, '')] [([], [None, None])] InternalError: Binder Error: Cannot concatenate lists of types INTEGER[] and VARCHAR[] - an explicit cast is required InternalError: Binder Error: No function matches the given name and argument types 'list_concat(INTEGER_LITERAL, INTEGER[])'. You might need to add explicit type casts. InternalError: Binder Error: Cannot concatenate types INTEGER[] and INTEGER - an explicit cast is required ``` ## Examples The items and extras of each cart as one list, with each form. Cart 2 has no extras: `list_concat` and its aliases return its items, and `||` returns NULL: ``` run(f"SELECT id, list_concat(items, extras), list_cat(items, extras), array_concat(items, extras), " f"array_cat(items, extras) FROM {TABLE} ORDER BY id LIMIT 10") run(f"SELECT id, items + extras, add(items, extras), items || extras FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag']), (2, ['cup'], ['cup'], ['cup'], ['cup']), (3, [], [], [], []), (4, [], [], [], [])] [(1, ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag'], ['pen', 'ink', 'bag']), (2, ['cup'], ['cup'], None), (3, [], [], []), (4, [], [], None)] ``` Prices and fees in one list: `BIGINT[]` joined with `DOUBLE[]` gives a `DOUBLE[]` list. Three lists with `||`: ``` run(f"SELECT id, list_concat(prices, fees), items || extras || ['receipt'] FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, [3.0, 4.0, 0.5], ['pen', 'ink', 'bag', 'receipt']), (2, [2.0], None), (3, [], ['receipt']), (4, [], None)] ``` Keep the carts with more than two things in them, counting extras: ``` run(f"SELECT id FROM {TABLE} WHERE len(list_concat(items, extras)) > 2 ORDER BY id LIMIT 10") ``` ``` [(1,)] ``` ## 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`). A list column is read whole, and every condition on a list runs in the Query Engine, so `list_concat()` and its operators are always computed there: - **In the `SELECT` list,** the scan reads both lists and the Query Engine joins them for each row it returns. - **In `WHERE`,** the scan sends both lists for every row, and a `FILTER` step in the Query Engine joins them 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 len(list_concat(items, extras)) > 2") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #2 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (len(list_concat(items, │ │ extras)) > 2) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ items │ │ extras │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Combine it with a pushed-down condition.** A list condition always runs after the scan. In `id > 100 AND len(list_concat(items, extras)) > 2`, `id > 100` is still evaluated in VAST DataBase, so the lists are joined only for the rows that pass it. - **Store the counts if you filter on them often.** Keep the number of items and extras in their own columns when you write the rows: a comparison of a column with a constant is pushed down to VAST DataBase. ## Limitations and errors - **Two arguments only.** `list_concat([1], [2], [3])` fails with `No function matches the given name and argument types 'list_concat(INTEGER[], INTEGER[], INTEGER[])'`. Chain `||` or `+`, or nest the calls. - **Lists only.** `list_concat` of a string, a number, a `BLOB`, a date or a boolean fails with `No function matches the given name and argument types`. To add one element, use [list_append](/develop/docs/dev-database-reference-sql-functions-lists-list-append) or [list_prepend](/develop/docs/dev-database-reference-sql-functions-lists-list-prepend). - **Element types must combine.** Cast one list first: `list_concat(CAST([1] AS VARCHAR[]), ['a'])`. - **Two untyped NULLs give an empty string.** `list_concat(NULL, NULL)` returns `''`. With typed NULLs or columns, the result is `[]`. - **No union or difference functions.** `array_union`, `array_except` and `list_except` fail with `Scalar Function with name ... does not exist!`. For a union without duplicates, use [list_distinct](/develop/docs/dev-database-reference-sql-functions-lists-list-distinct) on the joined list; its order isn't the order of the inputs. ``` run("SELECT list_concat([1], [2], [3])") run("SELECT list_concat(list_concat([1], [2]), [3]), list_concat(CAST([1] AS VARCHAR[]), ['a'])") run("SELECT list_distinct(list_concat([1, 2], [2, 3]))") ``` ``` InternalError: Binder Error: No function matches the given name and argument types 'list_concat(INTEGER[], INTEGER[], INTEGER[])'. You might need to add explicit type casts. [([1, 2, 3], ['1', 'a'])] [([3, 1, 2],)] ``` ## Similar functions | Function | Does | Result | | --- | --- | --- | | `list_concat([1, 2], [3])`, `[1, 2] + [3]` (and the concatenation operator) | joins two lists | `[1, 2, 3]` | | [list_append](/develop/docs/dev-database-reference-sql-functions-lists-list-append) `([1, 2], 3)` | adds an element at the end | `[1, 2, 3]` | | [list_prepend](/develop/docs/dev-database-reference-sql-functions-lists-list-prepend) `(0, [1, 2])` | adds an element at the start | `[0, 1, 2]` | | [flatten](/develop/docs/dev-database-reference-sql-functions-lists-flatten) `([[1, 2], [3]])` | joins the lists in a list of lists | `[1, 2, 3]` | | [list_distinct](/develop/docs/dev-database-reference-sql-functions-lists-list-distinct) `(list_concat([1, 2], [2, 3]))` | joins and drops duplicates | `[3, 1, 2]` | ``` run("SELECT list_concat([1, 2], [3]), [1, 2] || [3], [1, 2] + [3], list_append([1, 2], 3), list_prepend(0, [1, 2]), " "flatten([[1, 2], [3]]), list_distinct(list_concat([1, 2], [2, 3]))") ``` ``` [([1, 2, 3], [1, 2, 3], [1, 2, 3], [1, 2, 3], [0, 1, 2], [1, 2, 3], [3, 1, 2])] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the list functions. - [list_append](/develop/docs/dev-database-reference-sql-functions-lists-list-append), [list_prepend](/develop/docs/dev-database-reference-sql-functions-lists-list-prepend) and [flatten](/develop/docs/dev-database-reference-sql-functions-lists-flatten). - [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.