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

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 time_filter()
    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 time_filter()
    LIMIT 1;

Query result

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

For arrays that contain both null and non-null values, SQLake ignores those null values when working with the array. As such, the first array in coalesce_example2 is not considered a null value.

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