Node.js批量插入PostgreSQL报错，求解决方案及存储过程实现思路

一、Node.js端批量插入代码修正 #

你现有代码仅做了数据遍历赋值，未执行实际数据库插入操作，还存在this指向错误、未构建批量插入数据结构的问题。以下是基于pg模块的正确实现：

1. 依赖安装（未安装时执行） #

npm install pg

2. 修正后的路由代码 #

const { Pool } = require('pg');
const router = require('express').Router();

// 初始化数据库连接池
const pool = new Pool({
  user: '你的数据库用户名',
  host: 'localhost',
  database: '你的数据库名',
  password: '你的数据库密码',
  port: 5432,
});

router.post('/addItems', async (req, res) => {
  try {
    // 验证请求数据格式
    if (!Array.isArray(req.body)) {
      return res.status(400).json({ error: '请求数据必须为数组格式' });
    }

    // 构建批量插入的参数数组
    const insertValues = req.body.map(item => [
      item.purchase_id,
      item.item_code,
      item.item_name,
      item.description,
      item.category_id,
      item.location_id,
      item.invoice_no,
      item.warrantyend_Date,
      item.created_by,
      item.item_status,
      item.complain_id
    ]);

    // 生成参数化批量插入SQL（假设表名为items，字段顺序与参数对应）
    const placeholders = insertValues.map((_, idx) => 
      `($${idx*11 +1}, $${idx*11 +2}, $${idx*11 +3}, $${idx*11 +4}, $${idx*11 +5}, $${idx*11 +6}, $${idx*11 +7}, $${idx*11 +8}, $${idx*11 +9}, $${idx*11 +10}, $${idx*11 +11})`
    ).join(',');

    const insertQuery = `
      INSERT INTO items (
        purchase_id, item_code, item_name, description, category_id,
        location_id, invoice_no, warrantyend_date, created_by, item_status, complain_id
      ) VALUES ${placeholders}
      RETURNING *;
    `;

    // 扁平化参数数组，执行查询
    const flatParams = insertValues.flat();
    const result = await pool.query(insertQuery, flatParams);

    res.status(201).json({
      message: '批量插入成功',
      insertedRows: result.rows
    });
  } catch (error) {
    console.error('插入失败:', error);
    res.status(500).json({ error: '批量插入失败', details: error.message });
  }
});

module.exports = router;

代码说明 #

• 用async/await处理异步数据库操作，避免回调嵌套。
• 参数化查询防止SQL注入风险。
• RETURNING *返回插入后的行数据，方便前端校验结果。

二、PostgreSQL批量插入存储过程写法 #

提供两种实现方式，可根据场景选择：

方式1：自定义复合类型+数组参数 #

适合固定表结构的批量插入：

-- 创建与表字段匹配的复合类型
CREATE TYPE item_type AS (
  purchase_id VARCHAR,
  item_code VARCHAR,
  item_name VARCHAR,
  description TEXT,
  category_id VARCHAR,
  location_id VARCHAR,
  invoice_no VARCHAR,
  warrantyend_date DATE,
  created_by VARCHAR,
  item_status VARCHAR,
  complain_id VARCHAR
);

-- 创建批量插入存储过程
CREATE OR REPLACE PROCEDURE batch_insert_items(items_arr item_type[])
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO items (
    purchase_id, item_code, item_name, description, category_id,
    location_id, invoice_no, warrantyend_date, created_by, item_status, complain_id
  )
  SELECT * FROM unnest(items_arr);
END;
$$;

Node.js端调用示例

const callProcQuery = 'CALL batch_insert_items($1::item_type[])';
const itemsArr = req.body.map(item => ({
  purchase_id: item.purchase_id,
  item_code: item.item_code,
  item_name: item.item_name,
  description: item.description,
  category_id: item.category_id,
  location_id: item.location_id,
  invoice_no: item.invoice_no,
  warrantyend_date: item.warrantyend_Date,
  created_by: item.created_by,
  item_status: item.item_status,
  complain_id: item.complain_id
}));
await pool.query(callProcQuery, [itemsArr]);

方式2：JSONB数组参数（无需自定义类型） #

适合结构灵活的场景：

CREATE OR REPLACE PROCEDURE batch_insert_items_json(items_json JSONB)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO items (
    purchase_id, item_code, item_name, description, category_id,
    location_id, invoice_no, warrantyend_date, created_by, item_status, complain_id
  )
  SELECT 
    (item->>'purchase_id')::VARCHAR,
    (item->>'item_code')::VARCHAR,
    (item->>'item_name')::VARCHAR,
    (item->>'description')::TEXT,
    (item->>'category_id')::VARCHAR,
    (item->>'location_id')::VARCHAR,
    (item->>'invoice_no')::VARCHAR,
    (item->>'warrantyend_Date')::DATE,
    (item->>'created_by')::VARCHAR,
    (item->>'item_status')::VARCHAR,
    (item->>'complain_id')::VARCHAR
  FROM jsonb_array_elements(items_json) AS item;
END;
$$;

Node.js端调用示例

const callProcQuery = 'CALL batch_insert_items_json($1)';
await pool.query(callProcQuery, [JSON.stringify(req.body)]);

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