concat

Applies to VAST Cluster 5.5.

Functions › Text basics › Concatenation. Joins its arguments into one string, converting numbers, dates, booleans and other values to text and skipping NULLs. Given only lists, it joins the lists. It has no aliases; the concatenation operator || is similar but returns NULL if any part is NULL.

Syntax

concat(a [, b, ...])

Returns: VARCHAR, or a list when the arguments are lists.

Aliases: none.

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_concat. 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 people. Row 3 has no city, 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_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, "<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}.people"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, first_name VARCHAR, last_name VARCHAR, city VARCHAR, born BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada', 'Lovelace', 'London', 1815), "
    "(2, 'Grace', 'Hopper', 'New York', 1906), "
    "(3, 'José', 'Martí', NULL, 1853), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

concat joins its arguments in order, with nothing between them. Each argument that isn't a VARCHAR is first converted to its text form, and NULL arguments are skipped:

Because NULLs are skipped, concat never returns NULL for string arguments: a row whose values are all NULL gives '', or just the separators you passed. To put a separator between the values, and skip it next to NULLs, use concat_ws.

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 concat(NULL, '-', 'x'), concat('a', NULL), concat(NULL, NULL), typeof(concat(NULL, NULL)), concat('a')")
run("SELECT concat(1, 2), concat('a', 1, true, DATE '2026-10-16', 2.5), concat('\\xAA'::BLOB, '-', 'x')")
run("SELECT concat([1], [2]), concat(NULL, [1])")
run("SELECT concat()")
run("SELECT concat('a', [1, 2])")
run("SELECT concat([1], 'a')")

[('-x', 'a', '', 'VARCHAR', 'a')]
[('12', 'a1true2026-10-162.5', '\\xAA-x')]
[([1, 2], [1])]
InternalError: Binder Error: No function matches the given name and argument types 'concat()'. You might need to add explicit type casts.
InternalError: Binder Error: Cannot concatenate types VARCHAR and INTEGER[] - an explicit cast is required
InternalError: Binder Error: Cannot concatenate types INTEGER[] and VARCHAR - an explicit cast is required

Examples

A display name with the city. In row 3 the missing city is skipped, so the parentheses are empty; in row 4 only the separators are left:

run(f"SELECT id, concat(first_name, ' ', last_name, ' (', city, ')') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Ada Lovelace (London)'), (2, 'Grace Hopper (New York)'), (3, 'José Martí ()'), (4, '  ()')]

Numbers are converted to text, so you can build a label without a cast:

run(f"SELECT id, concat('user-', id, ' (born ', born, ')') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'user-1 (born 1815)'), (2, 'user-2 (born 1906)'), (3, 'user-3 (born 1853)'), (4, 'user-4 (born )')]

Find a person by full name:

run(f"SELECT id FROM {TABLE} WHERE concat(first_name, ' ', last_name) = 'Ada Lovelace' 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). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so concat() is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes concat() for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes concat() and keeps the rows that match. No rows are skipped in storage.

The plan of the full-name example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE concat(first_name, ' ', last_name) = 'Ada Lovelace'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (concat(first_name, ' ', │
│      last_name) = 'Ada    │
│         Lovelace')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         first_name        │
│         last_name         │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the parts, not the joined string. first_name = 'Ada' AND last_name = 'Lovelace' finds the same row as the full-name example, but each part is a comparison of a raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no FILTER step:

run(f"SELECT id FROM {TABLE} WHERE first_name = 'Ada' AND last_name = 'Lovelace' ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE first_name = 'Ada' AND last_name = 'Lovelace'")

[(1,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Join in the SELECT list, after filtering. concat() in the SELECT list runs only on the rows that pass the WHERE conditions, so filter on raw columns first and build the string for the rows you return.
• Store the joined value if you filter on it often. Compute it when you write the rows and keep it in its own column: full_name = 'Ada Lovelace' is pushed down, concat(...) = 'Ada Lovelace' isn't.

Limitations and errors

• Lists and other values can't be mixed. A list with a string, such as concat(['a', 'b'], '-', 'x'), fails with Cannot concatenate types VARCHAR[], VARCHAR and VARCHAR - an explicit cast is required. To put a list's text form into a string, use concat_ws, which converts lists to text.
• Lists must have the same element type. concat([1], ['a']) fails with Cannot concatenate lists of types INTEGER[] and VARCHAR[] - an explicit cast is required.
• At least one argument. concat() fails with No function matches the given name and argument types 'concat()'.
• BLOB values become text. concat with a BLOB returns a VARCHAR with the BLOB's text form, not bytes, even when every argument is a BLOB: concat('\xAA'::BLOB, '\xBB'::BLOB) is the VARCHAR '\xAA\xBB'. To join BLOB values as bytes, use the concatenation operator on two BLOB values.

run("SELECT concat(['a', 'b'], '-', 'x')")
run("SELECT concat_ws('-', ['a', 'b'], 'x')")

InternalError: Binder Error: Cannot concatenate types VARCHAR[], VARCHAR and VARCHAR - an explicit cast is required
[('[a, b]-x',)]

Similar functions

run("SELECT concat('a', NULL, 'b'), 'a' || NULL || 'b', concat_ws('-', 'a', NULL, 'b')")
run("SELECT concat([1, 2], [3]), [1, 2] || [3], concat_ws(',', [1, 2], [3]), list_concat([1, 2], [3])")

[('ab', None, 'a-b')]
[([1, 2, 3], [1, 2, 3], '[1, 2],[3]', [1, 2, 3])]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Text basics: all the text functions.
• The concatenation operator and concat_ws.
• list_concat: join lists.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.