如何从Node.js向PostgreSQL发送自定义类型对象数组？

问题分析 #

你的代码存在以下几个关键问题：

• 字段与类型不匹配：表定义中数组字段是speeds（类型t_speed[]），但代码中错误使用了heart_rates和t_heart_rate[]；
• SQL语句语法错误：INSERT语句未闭合（缺少末尾的)）；
• 自定义数组构造方式错误：直接拼接字符串或传递二维数组的方式不符合PostgreSQL对自定义类型数组的解析规则，且存在SQL注入风险。

修正后的代码 #

const postActivity = async (pool: any, req: any) => {
  let activity = req.body;
  let file = req.file;

  const tcxDestination = "C:/Users/frede/Desktop/DIPLOMKA/diplomka-app/Diplomka/BE/src";
  const newFilePath = `${tcxDestination}/${file.filename}`;

  const tcxContent = await fs.readFile(newFilePath, "utf-8");
  const { startTime, timeInSeconds, distanceInKm, heartRatesData, timestamps } = await extractDataFromGpx(tcxContent);

  // 构造符合t_speed自定义类型的数组元素：每个元素为[time, speed]格式的数组
  const speedsArray = timestamps.map((timestamp, index) => [new Date(timestamp), heartRatesData[index]]);

  try {
    await pool.query(
      `INSERT INTO activities(
        id_sportman, start_date, id_type_activity, distance, id_district, 
        name, gpx_file_name, gpx_file, time_in_seconds, speeds
      ) VALUES (
        $1, $2, $3, $4, 1, $5, $6, $7, $8, $9::t_speed[]
      )`,
      [
        activity.idSportman,
        startTime,
        activity.idTypeActivity,
        distanceInKm,
        activity.name,
        newFilePath,
        tcxContent,
        timeInSeconds,
        speedsArray // 直接传递二维数组，pg模块会自动映射为t_speed[]类型
      ]
    );
    console.log("活动数据插入成功");
  } catch (error) {
    console.error("插入失败:", error);
    throw error;
  }
};

关键修正点说明 #

1. 字段与类型对齐：将SQL中的heart_rates改为表定义的speeds，类型转换改为::t_speed[]，与自定义类型和表字段匹配。
2. 闭合SQL语句：补充了INSERT语句末尾的)，修复语法错误。
3. 安全构造数组：
   • 避免字符串拼接构造数组（防止SQL注入），直接传递由[time, speed]组成的二维数组；
   • pg模块会自动将这种二维数组结构映射为PostgreSQL的t_speed自定义行类型，再整体识别为t_speed[]数组类型。
4. 数据类型校验：确保timestamp能正确转换为PostgreSQL的date类型，heartRatesData元素为整数类型，避免类型转换失败。

备选方案（若上述方式失效） #

如果pg模块无法自动解析二维数组，可以使用PostgreSQL的ROW()构造函数显式声明每个自定义类型元素：

// 构造每个元素的ROW占位符，展开参数避免注入
const speedsPlaceholders = speedsArray.map((_, idx) => `ROW($${9 + idx*2}, $${9 + idx*2 +1})`).join(',');
const params = [
  activity.idSportman, startTime, activity.idTypeActivity, distanceInKm,
  activity.name, newFilePath, tcxContent, timeInSeconds,
  // 展开speedsArray的所有元素作为独立参数
  ...speedsArray.flat()
];

await pool.query(
  `INSERT INTO activities(
    id_sportman, start_date, id_type_activity, distance, id_district, 
    name, gpx_file_name, gpx_file, time_in_seconds, speeds
  ) VALUES (
    $1,$2,$3,$4,1,$5,$6,$7,$8, ARRAY[${speedsPlaceholders}]::t_speed[]
  )`,
  params
);

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