CASE Statement Postgres

Question

I'm attempting to PATCH an object via REST API. Something is wrong with my query...

// query 
var query = "UPDATE users SET \
            first_name = CASE WHEN $3 != '' THEN first_name = $3 ELSE first_name = first_name END, \
last_name = CASE WHEN $4 != '' THEN last_name = $4 ELSE last_name = last_name END, \
date_updated = now()\
WHERE username = $1"

// params
var params = [
    request.params.user_id,
    request.body.first_name || '',
    request.body.last_name || ''
];

// query runs 
sharedPgClient.query(query,params,function(err, res){
    callback(err, res);
});

What I get back is success, but every column is set to "false" except the username which I do not set. Nowhere do I set anythign to tru / false. I have tried with different values, etc but I am lost. Please help

Answer 1

The when and else clauses take values, not SQL fragments. first_name = $3, e.g., is a boolean expression which may either evaluate to true or to false, depending on the value of first_name. Instead, you just need to use the value you're trying to set:

var query = "UPDATE users SET \
            first_name = CASE WHEN $3 != '' THEN $3 ELSE first_name END, \
            // Here -----------------------------^
            last_name = CASE WHEN $4 != '' THEN $4 ELSE last_name END, \
            // And here ------------------------^
            date_updated = now()\
            WHERE username = $1"

Answer 2

What you are doing is using the result of the first_name = $3 expression in one case, and first_name = first_name in the other. Both are boolean.

What you want to write is probably:

... SET first_name = CASE WHEN $3 <> '' THEN $3 ELSE first_name END ...

Ditto for last_name.