--- title: "coalesce" slug: "dev-database-reference-sql-functions-conditional-and-cast-coalesce" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-conditional-and-cast-coalesce" --- > ## 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. # coalesce *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast) › 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, ...]) ``` | Argument | Type | Description | | --- | --- | --- | | `a`, `b`, ... | any | One or more values, of one type or of types that combine. They're tested in order. | **Returns:** the combined type of the arguments. ## 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_conditional_and_cast_coalesce`. 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 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, "") 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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-case): | Arguments | Result type | | --- | --- | | an `INTEGER` and a `BIGINT` column | `BIGINT` | | a `DOUBLE` and a `DECIMAL` column | `DOUBLE` | | `1` and `2.5` | `DECIMAL(11,1)` | | a `DATE` and a `TIMESTAMP` | `TIMESTAMP` | | `NULL::INTEGER` and the text literal `'5'` | `INTEGER` (the text is converted) | | a `VARCHAR` column and a number | Error: `Cannot mix values of type VARCHAR and INTEGER_LITERAL in COALESCE operator - an explicit cast is required` | It takes lists, `BLOB`, `UUID` and the other types as well, and a hundred arguments work as well as two. ## NULLs and edge cases | Input | Result | | --- | --- | | `coalesce(NULL, NULL)`, `coalesce(NULL)` | `NULL` | | `coalesce(NULL, NULL, 3)` | `3` | | `coalesce(1)` | `1` | | `coalesce('', 'x')` (an empty string isn't NULL) | `''` | | `coalesce('nan'::DOUBLE, 1)` (NaN isn't NULL) | `nan` | | `coalesce(1, 'a')` | `1`, an `INTEGER` | | `coalesce('a', 1)` | Error: `Could not convert string 'a' to INT32`: the text is converted to the number's type | | `coalesce(NULL, 'a', 1)` | Error: `Binder Error: Cannot mix values of type VARCHAR and INTEGER_LITERAL in COALESCE operator - an explicit cast is required` | | `coalesce()` | Error: `Parser Error: syntax error at or near ")"` | The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce#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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-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 | Function | Use it for | Example | Result | | --- | --- | --- | --- | | [ifnull](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-ifnull) | `coalesce` with exactly two arguments | `ifnull(NULL, 'b')` | `'b'` | | [nullif](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-nullif) | turning a placeholder into NULL | `coalesce(nullif('', ''), 'b')` | `'b'` | | [CASE](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-case) | any other test than "is NULL" | `CASE WHEN NULL IS NULL THEN 'b' END` | `'b'` | | [greatest](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-greatest) | the largest argument, skipping NULLs | `greatest(NULL, 1)` | `1` | ``` 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](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast): all the conditional and conversion functions. - [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.