Today was my last day of work at Pavlov Media. Sad day, the last day of a job. Anyways, I don’t keep this blog to talk about personal ramblings, so I’ll keep it short. One of the guys pointed out the large number of obscure tidbits I know about PostgreSQL and PHP development (most of the PHP is stuff you won’t see in a book, but as tiny repeatable timesavers or readability improvements in a large). So, I’m going to try to post random things I know about Postgres that I consider on the obscure side. Most of them are already well-documented, but stuff a newcomer would probably miss.

Postgres Tip #1: Use COALESCE and friends

The functions COALESCE(...), NULLIF(a,b), GREATEST(...), and LEAST(...) are all well-hidden ways to improve the readability of SQL queries. I’m going to talk about the first today.

COALESCE takes an arbitrary number of parameters, and simply returns the first one that’s not NULL. This has come in handy at work in two specific cases:

  1. If table vendors has field website, and table products (with a foreign key into vendors) also has field website, I can pull the most specific website of a product available with the SQL query:

    SELECT COALESCE(p.website, v.website) FROM products p JOIN vendors v...

    The alternative would have been to use a rather obtuse-looking CASE statement like this:

    SELECT CASE WHEN p.website IS NULL THEN v.website ELSE p.website END FROM products p JOIN vendors v...

  2. If a NULL value in a table is logically equivalent to another non-NULL value, it simplifies conditionals. For example, the Freeside billing system treats NULL and 0 both as “empty” in date fields, so I can check if a date is “empty” with only one comparison:

    ... WHERE COALESCE(date_field, 0) <> 0

    Again, the alternative is bulkier and harder to read:

    ... WHERE (date_field IS NOT NULL OR date_field <> 0)

    This is particularly useful for string fields, where the empty string and the NULL string are sometimes interchangeable. In this case, COALESCE really helps when dealing with someone else’s sloppy database design.

In both of these cases, the advantage is small in a toy example like this, but a SQL query with 3 default-values and 2 “maybe-NULL” conditionals, the difference is huge.

Advertisements