Updating postgres with if conditions on parameters

I am trying to do a postgres update using if conditions to check if a parameter is empty before attempting to update.

The desired result would be not to update when the parameter is an ampty string.

I cannot seem to figure out the correct way to do this. My first attempt is below:

$stmt = $db->prepare("
  UPDATE users SET
    fname = IF(trim(:fname) = '', fname, :fname)
  WHERE username = :username");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':fname', $fname);
$stmt->execute();

and then my second attempt was using:

$stmt = $db->prepare("
  UPDATE users SET fname =
    IF
      trim(:fname) =  ''
    THEN
      fname
    ELSE
      :fname
    END IF;
  WHERE username = :username
");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':fname', $fname);
$stmt->execute();

What is the correct way of doing this with IF statements?

Answer

• Probably the best way would be not to execute the statement; the PHP code already knows that the parameter is empty, so it can refrain from calling the $stmt->execute() (and surrounding code). That will also save the round-trip time to the database.

  if (trim($fname) != "") {
     $stmt = $db->prepare("
        UPDATE users
        SET fname = :fname
        WHERE username = :username");
     $stmt->bindParam(':username', $username);
     $stmt->bindParam(':fname', $fname);
     $stmt->execute();
  }
  

• If you do need to have the condition in the PostgreSQL query, note that IF is a statement, not an expression. The equivalent for expressions is CASE, something like:

  UPDATE users
  SET fname = CASE WHEN trim(:fname) = '' THEN fname ELSE :fname END
  WHERE username = :username
  

• In this particular case, you can also put the condition as part of the WHERE clause:

  UPDATE users
  SET fname = :fname
  WHERE username = :username and trim(:fname) <> ''