COALESCE

Returns the first value in a given list that is not null.

Syntax

COALESCE(VALUE1, VALUE2[, ...])

Arguments

VALUE

Type: any

A list of values of any type.

Returns

Type: same as input

The first VALUE that is not null.

Examples

Transformation job example

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data 
  MAP_COLUMNS_BY_NAME
    SELECT 
      COALESCE(3.4, 66.0, 744.7) AS coalesce_example1,
      COALESCE('a', null::string, 'z', 'h', 'k') AS coalesce_example2,
      COALESCE(null::date, date '2022-08-14', date '2022-02-05') AS coalesce_example3
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result

coalesce_example1	coalesce_example2	coalesce_example3
3.4	a	2022-08-14

Example with arrays

SQL

CREATE JOB function_operator_example
    ADD_MISSING_COLUMNS = true	
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data 
        MAP_COLUMNS_BY_NAME
    SELECT 
        COALESCE(ARRAY[1.1, 0.2], ARRAY[3.0]) AS coalesce_example1,
        COALESCE(ARRAY[null::double, 1.1, 0.2], ARRAY[3.0]) AS coalesce_example2,
        COALESCE([null::double], [1.1, 0.2], [3.0]) AS coalesce_example3
    FROM default_glue_catalog.upsolver_samples.orders_raw_data 
    WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
    LIMIT 1;

Query result

coalesce_example1	coalesce_example2	coalesce_example3
[1.1, 0.2]	[1.1, 0.2]	[1.1, 0.2]

In arrays containing null and non-null values, Upsolver ignores null values when working with the array. For example, the first array in coalesce_example2 is not considered a null value.

Conversely, an array containing only null values is considered null, so coalesce_example3 returns the second array provided.