如何在node-postgres中仅更新一列并避免其他列出现NULL值？

发布于 2025-01-12 08:51:17 字数 1489 浏览 0 评论 0原文

现在，在我的 API 中，如果我只想更新一个值（例如 email），其他值将变为 NULL，并且我还必须重写所有其他内容才能更新一个值事物。

JSON 对象是这样的：

{
    "username": "batman"
}

这将是结果：

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

因此，这迫使我编写整个 JSON 对象，以便其他值不会更新为 NULL：

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

这是 PUT 请求的回调函数:

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!" });
  }
};

有没有办法只更新一个值而不将其他值更新为 NULL？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

許願樹丅啲祈禱 2025-01-19 08:51:17

我按照本文的说明设法解决了我的问题：

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 *;

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 *;

回复收藏 0 原文

~没有更多了~