2019-08-26 PostgreSQL – COALESCE and NULLIF

After the last week’s long post I decided that I needed some rest, so today I only have a short tip.

It is a common need to say that we want the value of some variable x unless it is some kind of null value (depending on the language we use), in which case we want the value of some other variable y. The Lisp idiom for that is of course (or x y). In JavaScript, we usually say x || y, although this is risky if 0 is a valid value of x. (Hopefully, we will be able to say x ?? y in JS soon.)

In my case, however, I needed this in SQL (more specifically, in PostgreSQL, but that doesn’t matter now). Of course, SQL being SQL, I was sure that (a) it was possible, and (b) it was probably called something strange.

Of course, I was right. The COALESCE function does exactly the same thing as or or ||. And in the case you want e.g. the empty string to act as null, you can use another handy function, NULLIF, like this: NULLIF(value, '').

You’re welcome.

CategoryEnglish, CategoryBlog, CategoryPostgreSQL