BigQuery equivalent of COALESCE()?

Question

I'm in the process of converting some aggregate queries from Postgres to our new architecture in BigQuery. Is there an equivalent of COALESCE() in BigQuery?

Currently, I am converting a Postgres query statement like

coalesce(column1,'DEFAULT')

to

CASE
  WHEN column1 IS NOT NULL
     THEN column1
   ELSE 'DEFAULT'
END AS column1

which seems easy enough.

However converting a Postgres query statement with nested coalesce statements like

 count(distinct coalesce(
                coalesce(
                coalesce(column1,column2),
                                 column3),
                                 column4)))

would get much more messy if I used CASE statements all over the place, and also seems like the wrong thing to do.

Does BigQuery have a method equivilent to COALESCE(), or am I stuck writing the whole CASE statement equivalent?

Answer 1

You can use IFNULL function in BigQuery, which can be nested like that:

select ifnull(column1,
              ifnull(column2,'DEFAULT')) 
from 
(select string(NULL) as column1, 'y' as column2)

P.S. But omission of COALESCE in BigQuery is an oversight, I will fix it.

Update: As of 4/16/2015, COALESCE is available in BigQuery. https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#conditional_expressions