coalesce

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Replacing NULLs. Returns the first of its arguments that isn't NULL, and NULL only if every argument is NULL. Use it to put a default value in place of a missing one.

Syntax

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

Returns: the combined type of the arguments.

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_conditional_and_cast_coalesce. 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 contacts with some values missing. Contact 2 has an empty nickname, which isn't NULL:

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_conditional_and_cast_coalesce"'

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}.contacts"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, nickname VARCHAR, name VARCHAR, score INTEGER, bonus BIGINT, joined DATE, seen TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada', 'Ada Lovelace', 7, 100, DATE '2026-10-01', TIMESTAMP '2026-10-16 08:00:00'), "
    "(2, '', 'Grace Hopper', NULL, 50, NULL, TIMESTAMP '2026-10-17 09:30:00'), "
    "(3, NULL, 'Alan Turing', 3, NULL, DATE '2026-10-03', NULL), "
    "(4, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

coalesce(a, b, c) tests its arguments from left to right and returns the first one that isn't NULL. It stops there: later arguments aren't computed, so coalesce(1, CAST('x' AS INTEGER)) returns 1. If it has to reach a failing argument, the query fails: coalesce(NULL, CAST('x' AS INTEGER)) stops with Could not convert string 'x' to INT32.

The arguments are combined into one type, as in CASE:

It takes lists, BLOB, UUID and the other types as well, and a hundred arguments work as well as two.

NULLs and edge cases

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

run("SELECT coalesce(NULL, NULL), coalesce(NULL), coalesce(NULL, NULL, 3), coalesce(1), "
    "coalesce('', 'x'), coalesce('nan'::DOUBLE, 1), coalesce(1, 'a')")
run("SELECT coalesce(1, 2.5), typeof(coalesce(1, 2.5)), coalesce(NULL::INTEGER, '5'), "
    "coalesce(DATE '2026-10-16', TIMESTAMP '2026-10-16 12:00:00'), coalesce(1, CAST('x' AS INTEGER))")
run("SELECT coalesce('a', 1)")
run("SELECT coalesce(NULL, 'a', 1)")
run("SELECT coalesce(NULL, CAST('x' AS INTEGER))")

[(None, None, 3, 1, '', nan, 1)]
[(Decimal('1.0'), 'DECIMAL(11,1)', 5, datetime.datetime(2026, 10, 16, 0, 0), 1)]
OSError: Could not convert string 'a' to INT32
InternalError: Binder Error: Cannot mix values of type VARCHAR and INTEGER_LITERAL in COALESCE operator - an explicit cast is required
OSError: Could not convert string 'x' to INT32

Examples

A display name from the first of three sources that has one. Contact 2's empty nickname isn't NULL, so it's returned as it is; nullif turns it into NULL so the next source is used:

run(f"SELECT id, coalesce(nickname, name, 'unknown'), coalesce(nullif(nickname, ''), name, 'unknown') "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Ada', 'Ada'), (2, '', 'Grace Hopper'), (3, 'Alan Turing', 'Alan Turing'), (4, 'unknown', 'unknown')]

Defaults for numbers and dates. score is an INTEGER and bonus a BIGINT, so the result is a BIGINT; a DATE and a TIMESTAMP give a TIMESTAMP:

run(f"SELECT id, coalesce(score, 0), coalesce(score, bonus), typeof(coalesce(score, bonus)), "
    f"coalesce(joined, seen) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 7, 7, 'BIGINT', datetime.datetime(2026, 10, 1, 0, 0)), (2, 0, 50, 'BIGINT', datetime.datetime(2026, 10, 17, 9, 30)), (3, 3, 3, 'BIGINT', datetime.datetime(2026, 10, 3, 0, 0)), (4, 0, None, 'BIGINT', 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). What happens to coalesce() depends on its arguments:

• On constants only, such as coalesce(NULL, 2), it's computed once while the query is planned, and a comparison of a column with the result can still be pushed down to VAST DataBase.
• On a column in the SELECT list, the scan reads the column and the Query Engine computes coalesce() for each row it returns.
• On a column in WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes coalesce() and keeps the rows that match. No rows are skipped in storage.

The first plan has a FILTER step above the scan; the second, with coalesce() on constants, has none:

plan(f"SELECT id FROM {TABLE} WHERE coalesce(score, 0) > 2")
plan(f"SELECT id FROM {TABLE} WHERE score > coalesce(NULL, 2)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (COALESCE(score, 0) > 2) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           score           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Test the NULL case separately. coalesce(score, 0) > 2 keeps the same rows as score > 2, because the default 0 isn't greater than 2. To keep the NULL rows as well, write score > 2 OR score IS NULL. Both test one column against constants, which VAST DataBase can evaluate:

plan(f"SELECT id FROM {TABLE} WHERE score > 2 OR score IS NULL")
run(f"SELECT id FROM {TABLE} WHERE score > 2 OR score IS NULL ORDER BY id LIMIT 10")

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

• Store the default. If a column is always read with the same default, write the default when you load the rows.
• Put the argument that's most often set first. Testing stops at the first argument that isn't NULL.

Limitations and errors

• Text and numbers don't combine. A VARCHAR column with a number fails with Binder Error: Cannot mix values of type VARCHAR and INTEGER_LITERAL in COALESCE operator - an explicit cast is required, and an INTEGER column with a VARCHAR column with Cannot mix values of type INTEGER and VARCHAR in COALESCE operator. Cast one of them. can_cast_implicitly tells you whether two types combine without a cast.
• At least one argument. coalesce() fails with Parser Error: syntax error at or near ")".
• Empty strings, zeros and NaN aren't NULL. Turn a placeholder into NULL first with nullif.

run(f"SELECT coalesce(nickname, 0) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT coalesce(score, nickname) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, coalesce(CAST(score AS VARCHAR), nickname) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Binder Error: Cannot mix values of type VARCHAR and INTEGER_LITERAL in COALESCE operator - an explicit cast is required
InternalError: Binder Error: Cannot mix values of type INTEGER and VARCHAR in COALESCE operator - an explicit cast is required
[(1, '7'), (2, ''), (3, '3'), (4, None)]

Similar functions

run("SELECT coalesce(NULL, 'b'), ifnull(NULL, 'b'), coalesce(nullif('', ''), 'b'), "
    "CASE WHEN NULL IS NULL THEN 'b' END, greatest(NULL, 1)")

[('b', 'b', 'b', 'b', 1)]

Clean up:

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

[]
[]

See also

• Conditional and conversion functions: all the conditional and conversion functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.