|| (concatenation)

Applies to VAST Cluster 5.5.

Functions › Text basics › Concatenation. The || operator joins two strings, two lists or two BLOB values. Other values are converted to text first. If either side is NULL, the result is NULL; to skip NULLs instead, use concat.

Syntax

a || b

Returns: VARCHAR; a list for two lists; a BLOB for two BLOB values.

Aliases: none. The function form with NULL skipping is concat.

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_operator. 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 files, each with a directory, a name and an extension. File 3 has no extension, 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_operator"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, dir VARCHAR, name VARCHAR, ext VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/data/logs', 'app', 'log'), "
    "(2, '/data/raw', 'events', 'json'), "
    "(3, '/data', 'README', NULL), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

|| joins its two sides, with nothing between them. Chain it to join more: a || ' ' || b. What it returns depends on the types of the two sides:

The NULL rule applies to the whole chain: 'a' || 'b' || NULL is NULL, so one missing value empties the whole result. This is the main difference from concat, which skips NULLs.

Arithmetic binds tighter than ||, and || binds tighter than a comparison: 1 + 2 || 'x' is '3x', and 'a' || 'b' = 'ab' is true.

NULLs and edge cases

When a side is the bare literal NULL, the result is a NULL with no string type: typeof('a' || NULL) is "NULL", and an Arrow client sees a 32-bit integer column. A NULL from a VARCHAR column, as in Examples, gives a VARCHAR column.

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

run("SELECT 'a' || NULL, NULL || 'a', 'a' || 'b' || NULL, '' || '-x'")
run("SELECT 1 || 2, 1.5 || '-x', 'a' || 1, 'a' || true, DATE '2026-10-16' || '-x'")
run("SELECT [1] || [2], '\\xAA'::BLOB || '\\xBB'::BLOB, '\\xAA'::BLOB || '-x'")
run("SELECT 'a' || [1]")
run("SELECT ['a', 'b'] || '-x'")

[(None, None, None, '-x')]
[('12', '1.5-x', 'a1', 'atrue', '2026-10-16-x')]
[([1, 2], b'\xaa\xbb', '\\xAA-x')]
InternalError: Binder Error: Cannot concatenate types VARCHAR and INTEGER[] - an explicit cast is required
InternalError: Binder Error: Cannot concatenate types VARCHAR[] and VARCHAR - an explicit cast is required

Examples

The full path of each file. File 3 has no extension, so its whole path is None:

run(f"SELECT id, dir || '/' || name || '.' || ext FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '/data/logs/app.log'), (2, '/data/raw/events.json'), (3, None), (4, None)]

To keep the path when the extension is missing, use the NULL rule on the optional part only: '.' || ext is NULL when ext is, and coalesce replaces it with an empty string:

run(f"SELECT id, dir || '/' || name || coalesce('.' || ext, '') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '/data/logs/app.log'), (2, '/data/raw/events.json'), (3, '/data/README'), (4, None)]

Find a file by its path without the extension:

run(f"SELECT id FROM {TABLE} WHERE dir || '/' || name = '/data/logs/app' 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 an expression built from a column, so || on a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine joins them for each row it returns.
• In WHERE, the scan sends the columns 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 path example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE dir || '/' || name = '/data/logs/app'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (((dir || '/') || name) = │
│     '/data/logs/app')     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            dir            │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the parts, not the joined string. dir = '/data/logs' AND name = 'app' finds the same file, 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 dir = '/data/logs' AND name = 'app' ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE dir = '/data/logs' AND name = 'app'")

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

• Store the joined value if you filter on it often. Compute it when you write the rows and keep it in its own column: path = '/data/logs/app.log' is pushed down, dir || '/' || name || '.' || ext = '...' isn't.
• Combine it with a pushed-down condition. In id > 1 AND dir || '/' || name = '...', id > 1 is still evaluated in VAST DataBase, so || runs only on the rows that pass it.

Limitations and errors

• A list joins only with a list. ['a', 'b'] || '-x' and 'a' || [1] fail with Cannot concatenate types ... - an explicit cast is required. To put a list's text form into a string, use concat_ws, which converts lists to text.
• One NULL makes the result NULL. Use concat or concat_ws to skip NULLs, or coalesce on the optional parts.
• BLOB and string give a string. Only two BLOB values give a BLOB. With a string or a number on the other side, the BLOB is converted to its text form.

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

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

Similar functions

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

[(None, 'ab', '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.
• concat and concat_ws: join with NULLs skipped.
• Concatenation of lists and list_concat.
• Comparing and concatenating BLOB values.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.