Bigquery IFNULL() Error: No matching signature for function IFNULL for argument types

Problem 🔍

IFNULL function is used to substitute NULL values in an column or expression with a valid value. However, In Bigquery, the IFNULL function arguments should be of the same type, else it will throw the below error message:

Error: No matching signature for function IFNULL for argument types: 
STRING, INT64. Supported signature: IFNULL(ANY, ANY) at [4:9]

Solution 💡

Make sure all the arguments within the IFNULL function has to be the same datatype

IFNULL(expr, null_result)

eg. if the expr returns string datatype, then null_result also has to be string datatype

Example:

Convert the below query

SELECT IFNULL(string_COL, int_COL)

to explicitly cast the second column, so both the arguments are of the same STRING type

SELECT IFNULL(string_COL, CAST(int_COL AS STRING))

This should resolve the issue !

More Troubleshooting tips ⚡

• Though we have shown one example in this guide, you can use the same idea to resolve all the other datatype differences as well. eg. INT64 vs STRING, FLOAT vs NUMERIC, INT64 vs NUMERIC, DATE vs STRING,..

Ryan-Dallas

posted on 07 Feb 19