Node.js+PostgreSQL项目如何自动计算years字段为当前日期减creationDate

1. 优先方案：修改PostgreSQL表结构（无需额外处理业务代码，自动维护years值） #

你当前years字段类型设置为DATE不符合需求，日期差值应为整数类型，改成自动计算的生成列即可实现插入、更新creationDate时自动刷新years值：

新表创建语句修改： #

CREATE TABLE IF NOT EXISTS bands (
   id serial PRIMARY KEY,
   name VARCHAR UNIQUE NOT NULL,
   creationDate DATE NOT NULL,
   -- 自动计算距离当前日期的年数
   years INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM AGE(CURRENT_DATE, creationDate))::INT) STORED
);

已创建表的修改语句： #

-- 删除原有错误类型的years字段
ALTER TABLE bands DROP COLUMN years;
-- 新增自动计算的生成列
ALTER TABLE bands ADD COLUMN years INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM AGE(CURRENT_DATE, creationDate))::INT) STORED;

2. 修复Node代码中的已知错误 #

2.1 修复POST接口插入SQL错误 #

原有插入语句写了3个字段但只传了2个参数，去掉不需要手动插入的years字段即可：

const newBand = await pool.query(
  -- 移除插入字段列表中的years
  "INSERT INTO bands (name, creationDate) VALUES ($1, $2) RETURNING *",
  [name, creationDate]
);
// 建议返回rows里的实际数据
res.json(newBand.rows[0]);

2.2 修复PUT接口表名错误 #

原有更新语句表名误写为band，修正为bands，生成列会在creationDate更新时自动刷新：

const updateTodo = await pool.query(
  -- 修正表名band为bands
  "UPDATE bands SET name = $1, creationDate = $2 WHERE id = $3 RETURNING *",
  [name, creationDate, id]
);

2.3 优化GET接口返回值 #

原有直接返回allBands会包含数据库返回的元信息，建议仅返回业务需要的行数据：

app.get("/bands", async (req, res) => {
  try {
    const allBands = await pool.query("SELECT * FROM bands");
    // 仅返回行数据
    res.json(allBands.rows);
  } catch (err) {
    console.error(err.message);
  }
});

可选替代方案（不使用生成列） #

如果不想修改表结构为生成列，可直接在插入和更新语句中手动计算years值，需同步修改POST和PUT的SQL：

POST接口修改： #

const newBand = await pool.query(
  "INSERT INTO bands (name, creationDate,years) VALUES ($1, $2, EXTRACT(YEAR FROM AGE(CURRENT_DATE, $2))::INT) RETURNING *",
  [name, creationDate]
);

PUT接口修改： #

const updateTodo = await pool.query(
  "UPDATE bands SET name = $1, creationDate = $2, years = EXTRACT(YEAR FROM AGE(CURRENT_DATE, $2))::INT WHERE id = $3",
  [name, creationDate, id]
);

注意该方案需要你每次更新creationDate时都手动计算years，维护成本更高，优先推荐生成列方案。

内容的提问来源于stack exchange，提问作者learningbtw