array_to_string

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Joins the elements of a list into one string, with a separator between them. NULL elements are skipped. array_to_string_comma_default does the same with a comma.

Syntax

array_to_string(list, separator)
array_to_string_comma_default(list)

Returns: VARCHAR. NULL if the list is NULL, empty, or holds only NULLs.

Aliases: array_to_string_comma_default(list) is array_to_string(list, ',').

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_lists_array_to_string. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), 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 support tickets, each with a list of labels and a list of error codes. Ticket 2 has NULL elements, ticket 3 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_array_to_string"'

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, "<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}.tickets"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, labels VARCHAR[], codes BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['bug', 'ui'], [404, 500]), "
    "(2, ['ops', NULL, 'db'], [NULL, 7]), "
    "(3, [], []), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

array_to_string converts each element of the list to text and joins the results, in order, with separator between them: array_to_string([1, 2, 3], ',') is '1,2,3'.

• NULL elements are skipped, and no separator is written for them: array_to_string(['a', NULL, 'b'], ',') is 'a,b'.
• Nothing to join gives NULL, not ''. An empty list, a list of NULLs and a NULL list all return NULL. Wrap the call in coalesce(..., '') if you need an empty string.
• How elements are written: true and false for booleans, 2026-10-16 for a date, nan and inf for special doubles, 1e+308 for a large double, and an inner list in brackets ([1, 2]).

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT array_to_string(NULL, ','), array_to_string([], ','), array_to_string([NULL, NULL], ','), "
    "array_to_string(['a', NULL, 'b'], ','), array_to_string(['a'], NULL)")
run("SELECT array_to_string(['a', 'b'], ''), array_to_string(['a', 'b'], ' | '), "
    "array_to_string([0.5, 1e308, -0.0], ','), array_to_string(['NaN'::DOUBLE, 'inf'::DOUBLE], ',')")
run("SELECT array_to_string([true, false], ','), array_to_string([DATE '2026-10-16'], ','), "
    "array_to_string([[1, 2], [3]], ';'), array_to_string(['\u00e9', '\U0001F1EB\U0001F1F7'], '\u00b7')")
run("SELECT array_to_string(['a', 'b'])")

[(None, None, None, 'a,b', 'a')]
[('ab', 'a | b', '0.5,1e+308,0.0', 'nan,inf')]
[('true,false', '2026-10-16', '[1, 2];[3]', 'é·🇫🇷')]
InternalError: Binder Error: Macro function array_to_string(arr, sep) requires 2 positional arguments, but a single positional argument was provided.

Examples

The labels and codes of each ticket as text. The NULL elements of ticket 2 are skipped; the empty lists of ticket 3 and the NULL lists of ticket 4 give NULL:

run(f"SELECT id, array_to_string(labels, ', '), array_to_string(codes, '-') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'bug, ui', '404-500'), (2, 'ops, db', '7'), (3, None, None), (4, None, None)]

The same labels joined with a comma, and an empty string instead of NULL:

run(f"SELECT id, array_to_string_comma_default(labels), coalesce(array_to_string(labels, ','), '') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'bug,ui', 'bug,ui'), (2, 'ops,db', 'ops,db'), (3, None, ''), (4, None, '')]

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 list columns whole, and array_to_string() is always computed by the Query Engine:

• In the SELECT list, the scan sends the list column and the Query Engine joins its elements for each row it returns.
• In WHERE, a FILTER step in the Query Engine joins the elements of every row the scan sends and keeps the rows that match. No rows are skipped in storage.

array_to_string is a macro over list aggregation, which is why a separator of the wrong type fails with an error that names string_agg (see Limitations and errors). The plan of a LIKE on the joined text shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE array_to_string(labels, ',') LIKE '%ui%'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ contains(list_aggr(labels,│
│    'string_agg'), 'ui')   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           labels          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Search the list, not its text. To find rows with a given element, use list_contains or list_has_any: they compare elements directly and don't build a string for every row. array_to_string(labels, ',') LIKE '%ui%' also matches 'guide'.
• Pair a list condition with a pushed-down one. A comparison of a plain column with a constant, such as id > 1, is evaluated in VAST DataBase, so the rows it rules out never reach the FILTER step.

run(f"SELECT id FROM {TABLE} WHERE list_contains(labels, 'ui') ORDER BY id LIMIT 10")

[(1,)]

Limitations and errors

• The first argument must be a list. A string fails with Type VARCHAR with value 'abc' can't be cast to the destination type LIST. A number, date, boolean or BLOB fails with Unimplemented type for cast, for example (INTEGER -> VARCHAR[].
• The separator must be text, and is required. An integer separator fails with No matching aggregate function; leaving it out fails with Macro function array_to_string(arr, sep) requires 2 positional arguments. array_to_string_comma_default takes exactly one argument: a second one fails with requires a single positional argument, but 2 positional arguments were provided.
• Other SQL dialects' names aren't available. array_join and list_join fail with Scalar Function with name ... does not exist!. Use array_to_string.

run("SELECT array_to_string('abc', ',')")
run("SELECT array_to_string(['a', 'b'], 1)")
run("SELECT array_join(['a', 'b'], ',')")

OSError: Type VARCHAR with value 'abc' can't be cast to the destination type LIST
InternalError: Binder Error: No matching aggregate function
InternalError: Catalog Error: Scalar Function with name array_join does not exist!

Similar functions

run("SELECT array_to_string(l, ','), array_to_string(l, ''), array_to_string_comma_default(l), "
    "list_string_agg(l), list_aggregate(l, 'string_agg', '|') FROM (SELECT ['a', NULL, 'b'] AS l)")

[('a,b', 'ab', 'a,b', 'a,b', 'a|b')]

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: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.