Search code examples
sqlpostgresqlcoalesce

COALESCE in postgresql conditional displaying seemingly undocumented behavior?

I have looked at the COALESCE documentation and it mentions the typical case of using COALESCE to make default/situational parameters, e.g.

COALESCE(discount, 5)

which evaluates to 5 if discount is not defined as something else.

However, I have seen it used where COALESCE actually evaluated all the arguments, despite the documentation explicitly saying it stops evaluating arguments after the first non-null argument.

Here is an example similar to what I encountered, say you have a table like this:

id | wind | rain | snow
1  | null |   2  |  3
2  |   5  | null |  6
3  | null |   7  |  2

Then you run

SELECT *
FROM weather_table
WHERE
    COALESCE(wind, rain, snow) >= 5

You would expect this to only select rows with wind >= 5, right? NO! It selects all rows with either wind, rain or snow more than 5. Which in this case is 2 rows, specifically these two:

2  |   5  | null |  6
3  | null |   7  |  2

Honestly, pretty cool functionality, but it really irks me that I couldn't find any example of this online or in the documentation.

Can anyone tell me what's going on? Am I missing something?

Solution

  • You would expect this to only select rows with wind >= 5, right?

    No, I expect it to select rows with what the Coalesce function returns.

    The Coalesce function delivers the value of the first non-null parameter. You had Coalesce(wind,rain,snow). The first row had (null,2,3), so coalesce returned 2. The second row had (5,null,6) so returned 5. The third row had (null,7,2) so returned 7.

    The last two rows meet the condition >=5, so 2 rows are retrieved.

    Notice that the value for snow was never returned in your example, because either wind or rain always had a value.