How to update only one column and avoid NULL values for others in node-postgres?

Question

Right now, in my API if I only want to update one value like email, other values become NULL and I have to rewrite all the other stuff as well just to update one thing.

The JSON object is this:

{
    "username": "batman"
}

This will be the result:

{
    "message": "User Updated Successfully!",
    "user": {
        "user_id": 44,
        "name": null,
        "username": "batman",
        "email": null,
        "phone": null,
        "website": null
    }
}

So, this obligates me to write the whole JSON object so that other values don't update to NULL:

{
    "name": "batman",
    "username": "batman",
    "email": "[email protected]",
    "phone": "0123456789",
    "website": "batman.com"
}

This is the callback function for the PUT request:

const updateUser = async (req, res) => {
  const id = parseInt(req.params.id);
  const { name, username, email, phone, website } = req.body;
  try {
    const update = await db.query("UPDATE users SET name = $1, username = $2, email = $3, phone = $4, website = $5 WHERE user_id = $6 RETURNING *", [
      name,
      username,
      email,
      phone,
      website,
      id,
    ]);
    res
      .status(200)
      .json({ message: "User Updated Successfully!", user: update.rows[0] });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: "Something Went Wrong!" });
  }
};

Is there any way to update only one value without the others updating to NULL?

Nima Zarei · Accepted Answer · 2022-03-08 09:06:26Z

1

I managed to solve my problem by following this article's instruction:

Conditional update in PostgreSQL

UPDATE users 
    SET 
        name = COALESCE (NULLIF($1, ''), name),
        username = COALESCE (NULLIF($2, ''), username),
        email = COALESCE (NULLIF($3, ''), email),
        phone = COALESCE (NULLIF($4, ''), phone),
        website = COALESCE (NULLIF($5, ''),
        website) WHERE user_id = $6
RETURNING *;

answered Mar 8, 2022 at 9:06

Nima Zarei

1,2863 gold badges17 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update only one column and avoid NULL values for others in node-postgres?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related