Postgres hangs on large table when trying to update multiple rows with a CASE statement

The problem was ..

The statement:

CASE  WHEN column_b = '123' THEN 1  WHEN column_b = '345' THEN 2END;

.. is just short for:

CASE  WHEN column_b = '123' THEN 1  WHEN column_b = '345' THEN 2  ELSE NULLEND

Meaning, without a WHERE clause, your UPDATE statement is not just "trying", it actually updates every single row in the table, most of them to NULL.
Maybe, a NOT NULL constraint on the column prevented data loss ...

The better solution is ..

I'll have thousands of updates at once and would prefer to put them in one statement.

Much faster (and shorter) for large sets:

UPDATE foobar fSET    column_a = val.aFROM  (   VALUES     (123, 1)    ,(345, 2)   ) val(b, a)WHERE f.column_b = val.b

Joining to a set beats iterating through a long list of CASE branches for every row easily. The difference will grow rapidly with longer lists.

Also, be sure to have an index on column_b either way.

You can replace the VALUES expression with any table, view or subselect yielding appropriate rows.

Note:
I am assuming that column_a and column_b are of type integer. In this case, the single quotes around '123' in your question were never helpful. You'd better use a numeric literal instead of a string literal. (Even though it works with string literals, too.)

A string literal like '123' defaults to unknown type.
A numeric literal like 123 defaults to integer - or bigint / numeric if the number is too big.

If you were dealing with non-default data types, you would have to cast explicitly. Would look like:

...FROM  (   VALUES     ('123'::sometype, '1'::sometype)  -- first row defines row type    ,('345', '2')   ) val(b, a)...

I'm keeping this question up in case anyone runs into this issue.

This query was the culprit:

UPDATE foobar SET column_a = CASE  WHEN column_b = '123' THEN 1  WHEN column_b = '345' THEN 2END;

Problem is that it's missing a WHERE statement so it's trying to update all rows. With large databases this can be an issue, in my case it just timed out. As soon as I added the where statement in there it fixed the issue.

Here's the solution:

UPDATE foobar SET column_a = CASE   WHEN column_b = '123' THEN 1   WHEN column_b = '345' THEN 2ENDWHERE column_b IN ('123','345')