Conditional update in PostgreSQL (updated!)

What should you do if you only want your column to be updated if the supplied value is valid?

Let’s say we are updating a user’s profile. There are many different fields:, name, email address, phone number, etc. But the user is changing only the email address, so there’s no need to update all the other values in the data row. What shall we do?

In this article I’m assuming that you’re generating your PostgreSQL query as text, then sending it to the PostgreSQL server. Granted, this isn’t the most SQL-ish solution in the world. The super-proper tight-ass method is to store all queries in SQL and call them as methods, using parameters. But this isn’t the preferred way of most programmers, as generating queries in PHP or Node.js is usually simpler and provides more control. It’s not what you’d do in a really serious project that needs high security, such as a national election’s database (albeit some companies might disagree with me here), but it’s more than adequate for your average office project.

The non-elegant solution of updating your data: get the user’s complete record, modify the fields that need to be modified in PHP, Node.js or whatever you’re using, then send this data to PostgreSQL, in a simple UPDATE query. Thus you’re overwriting everything, but some fields with their original values, so they don’t actually change.

UPDATE users SET
  name = 'John Doe',
  email = 'johndoe@whatever.com',
  phone = '12345678910',
  city = 'Whatever City'
WHERE id = 3242;

This is good enough to let you pass an exam, but a strict tech lead might spin you around by your tail for a few minutes for this. Good programmers don’t shovel data in their code. That’s what the database is for.

You can actually make an update conditional. If you don’t have a value for any of the columns, you can simply pass an empty string and let PostgreSQL ignore it. The heart of the solution is the NULLIF() operator.

This handy little tool turns any value into a NULL (as in PostgreSQL’s NULL, not JavaScript null) and allows to throw away unnecessary values. Here’s a basic example:

SELECT NULLIF('kitten', 'kitten') AS kitten

The result set will be:

kitten|
------|
      |

Why? The NULLIF() operator takes the first parameter and compares it with the second one. If the two are identical, it returns NULL, otherwise returns the first value. In this case the string 'kitten' equals to 'kitten', so the result is NULL. But if you write this:

SELECT NULLIF('kitten', 'not kitten') AS kitten

the result will be:

kitten|
------|
kitten|

How to use NULLIF() to turn an UPDATE row into a conditional one? By combining it with another operator, COALESCE(). This operator detects NULL values and replaces them with a substitute. For example:

SELECT COALESCE(NULL, 'kitten') AS kitten

will be:

kitten|
------|
kitten|

because COALESCE replaced the NULL value with the string 'kitten'. If it wasn’t NULL, it returned that value:

SELECT COALESCE('not kitten', 'kitten') AS kitten
kitten    |
----------|
not kitten|

Now let’s see the two together:

SELECT COALESCE(NULLIF('kitten', 'kitten'), 'kitten') AS kitten

Here NULLIF() checks if 'kitten' equals to 'kitten' and since it does, it returns NULL. But COALESCE() intercepts the NULL value and replaces it with the string 'kitten'. A pretty pointless operation at the end, but I hope it helps to understand the logical process.

Now let’s see how can we use this strange contraption to complicate our simpleUPDATE:

UPDATE users SET
  name = COALESCE(NULLIF('New Name', ''), name),
  email = COALESCE(NULLIF('new@email.com', ''), email),
  phone = COALESCE(NULLIF('987654321', ''), phone),
  city = COALESCE(NULLIF('New City', ''), city)
WHERE id = 3242;

Obviously New Name, New City and all these strings are the new values we’re trying to insert. If any of these strings were empty, NULLIF() would convert them to NULL. Then COALESCE() replaces this NULL value with the field’s current value, and therefore nothing changes if it isn’t needed.

You can now send an empty string for all the fields you don’t want to update. In this example only name will be updated:

UPDATE users SET
  name = COALESCE(NULLIF('New Name', ''), name),
  email = COALESCE(NULLIF('', ''), email),
  phone = COALESCE(NULLIF('', ''), phone),
  city = COALESCE(NULLIF('', ''), city)
WHERE id = 3242;

With this solution you can avoid not only shoveling data to and back, but it’s also a sure way to avoid string escape problems or trouble with special characters.

But of course a good programmer escapes his input strings, for the sake of his own sanity. So let’s add a few E’s:

UPDATE users SET
  name = COALESCE(NULLIF(E'New Name', E''), name),
  email = COALESCE(NULLIF(E'', ''), email),
  phone = COALESCE(NULLIF(E'', ''), phone),
  city = COALESCE(NULLIF(E'', ''), 'null'), city)
WHERE id = 3242;

This method only works with primitive values though. For JSON values, arrays and other complex data types it’s a much more complicated matter, and I’m going to publish another article about them soon.

Update: how to complicate simple things further?

A few days after I published this article I came up with a stored function which is the current “perfect solution”.

CREATE OR REPLACE FUNCTION updateIfChanged(newValue ANYELEMENT, field ANYELEMENT, allowNull BOOLEAN DEFAULT FALSE) RETURNS ANYELEMENT AS $$
BEGIN
    IF 
        (allowNull = FALSE AND newValue IS NULL) OR 
        LOWER(newValue::varchar) = 'null' OR
        LOWER(newValue::varchar) = 'undefined'
    THEN
        RETURN field;
    ELSE
        RETURN newValue;
    END IF;
END;
$$ LANGUAGE plpgsql;

Here’s how to use it:

UPDATE users SET
  name = updateIfChanged('John Doe', name),
  email = updateIfChanged(NULL, email),
  phone = updateIfChanged(NULL, phone),
  city = updateIfChanged(NULL, city)
WHERE id = 3242;

Now you can simply pass a NULL value to indicate that the field hasn’t changed. The function also filters out 'null' and 'undefined' as string (varchar) values, just in case something ugly seeps in from the frontend.

A discussion of how my backend manages connection with PostgreSQL would fall outside of the scope of this article, but in a nutshell, here’s what it does: when I receive a POST request from the frontend, I use express-validator to validate each field (in this case, name, email, phone and city) and I set null to every missing node using default(). After validation I have a req.body with every necessary form field, but only the ones need to be updated have a value. If I want to allow NULL value for a field, I can set the allowNull argument to TRUE.