Node+Express中PostgreSQL双表插入事务报错：pid违反非空约束

嘿，我看了你的代码，马上就发现了几个导致pid为null的问题，咱们一个个来修复：

问题根源分析 #

1. 查询结果取值错误：PostgreSQL的RETURNING子句返回的结果存在res.rows数组里，哪怕只返回一行，你也得用res.rows[0].pid来获取具体值，直接用res.rows.pid肯定拿不到数据，自然就是null了。
2. 变量名冲突：你内层回调里用了res作为参数，直接覆盖了外层路由的res对象，这不仅会导致后续无法正确返回响应，还容易让你混淆变量。
3. 参数赋值错误：你定义的data对象里是author: req.body.username，但后面拼参数的时候却写了data.username，这会拿到undefined，虽然这不是当前null错误的直接原因，但也是个潜在bug。

修复后的回调版代码 #

先给你修复了上述问题的回调版本代码，保持你原来的事务结构：

exports.addPost = (req, res) => {
  db.query('BEGIN', err => {
    if (shouldAbort(err)) {
      // 记得在这里也要处理响应，避免请求挂起
      return res.status(500).json({ error: 'Failed to start transaction' });
    }

    const queryPost = 'INSERT INTO table1 (post_type, created_on) VALUES($1, NOW()) RETURNING pid';
    // 把内层的res改成result，避免覆盖外层响应对象
    db.query(queryPost, ['keyword'], (err, result) => {
      if (shouldAbort(err)) {
        return res.status(500).json({ error: 'Failed to insert into table1' });
      }

      // 正确获取返回的pid
      const pid = result.rows[0].pid;

      const data = {
        author: req.body.username,
        title: req.body.title,
        description: req.body.description,
        body: req.body.body,
        category: req.body.category,
        search_volume: req.body.search_volume,
        is_deleted: req.body.is_deleted
      };

      const insertKeywordText = `INSERT INTO table2 (pid, author, title, description, body, category, search_volume, is_deleted, created_on) 
                                 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, NOW())`;
      // 修正参数：用data.author代替data.username
      const insertKeywordValues = [
        pid, 
        data.author, 
        data.title, 
        data.description, 
        data.body, 
        data.category, 
        data.search_volume, 
        data.is_deleted
      ];

      // 内层回调参数改用insertResult，避免变量冲突
      db.query(insertKeywordText, insertKeywordValues, (err, insertResult) => {
        if (shouldAbort(err)) {
          return res.status(500).json({ error: 'Failed to insert into table2' });
        }

        db.query('COMMIT', err => {
          if (err) {
            console.error('Error committing transaction', err.stack);
            return res.status(500).json({ error: 'Failed to commit transaction' });
          }
          // 使用外层的res返回成功响应
          res.status(200).json({ message: 'Post added successfully', pid });
        });
      });
    });
  });
};

更优雅的CTE实现方案 #

其实你最初想要的“一次查询向两个表插入数据”的需求，完全可以用PostgreSQL的CTE（公共表表达式）来实现，不用手动管理事务，代码更简洁还能避免回调嵌套的问题：

exports.addPost = (req, res) => {
  // 解构请求体参数，让代码更清晰
  const { username, title, description, body, category, search_volume, is_deleted } = req.body;
  
  // CTE语句：先插入table1并返回pid，再用这个pid插入table2
  const cteQuery = `
    WITH inserted_post AS (
      INSERT INTO table1 (post_type, created_on) 
      VALUES ($1, NOW()) 
      RETURNING pid
    )
    INSERT INTO table2 (pid, author, title, description, body, category, search_volume, is_deleted, created_on)
    SELECT pid, $2, $3, $4, $5, $6, $7, $8, NOW()
    FROM inserted_post
    RETURNING *;
  `;

  // 准备参数数组
  const values = ['keyword', username, title, description, body, category, search_volume, is_deleted];

  db.query(cteQuery, values, (err, result) => {
    if (err) {
      console.error('Error executing query', err.stack);
      return res.status(500).json({ error: err.message });
    }
    res.status(200).json({ 
      message: 'Post added successfully', 
      data: result.rows[0] 
    });
  });
};

这个CTE方案的优势在于：

• 两个插入操作在同一个SQL语句中执行，PostgreSQL会自动保证原子性，不需要手动调用BEGIN/COMMIT
• 避免了回调嵌套带来的变量冲突和取值错误问题
• 代码结构更清晰，可读性更强

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