Node.js中如何用参数化查询向PostgreSQL Upsert带DEFAULT的记录

问题描述 #

使用Node.js的pg库对接PostgreSQL，通过单条语句批量Upsert多行数据，以非空主键id（BIGSERIAL类型）判断插入或更新。直接在SQL中写DEFAULT作为id值时查询正常，但用参数化查询传入字符串'DEFAULT'时触发类型转换错误：

{
  "name": "error",
  "severity": "ERROR",
  "code": "22P02",
  "where": "unnamed portal parameter $4 = '...'",
  "file": "numutils.c",
  "routine": "pg_strtoint64"
}

问题原因 #

PostgreSQL中的DEFAULT是SQL关键字，用于触发列的默认值（此处为BIGSERIAL的自增序列）。但参数化查询会将传入的'DEFAULT'视为普通字符串常量，而id是BIGINT类型（BIGSERIAL本质是BIGINT+自增序列），无法将字符串转换为数值类型，因此抛出22P02错误。

解决方案 #

方案1：动态构造SQL，区分自增行 #

遍历待插入数据，对无id的行直接在SQL中写入DEFAULT，有id的行使用参数占位符，避免将DEFAULT作为参数传递。

示例代码：

// 待处理的批量数据
const batchData = [
  { id: 1, col1: 'abc', col2: 'def' },
  { col1: 'ghi', col2: 'jkl' } // 无id，使用自增
];

// 构造VALUES部分的占位符
const valuePlaceholders = batchData.map((item, idx) => {
  const paramOffset = idx * 3;
  if (item.id) {
    // 有id的行，使用三个参数占位符
    return `($${paramOffset + 1}, $${paramOffset + 2}, $${paramOffset + 3})`;
  } else {
    // 无id的行，id用DEFAULT，其余两个参数占位符
    return `(DEFAULT, $${paramOffset + 1}, $${paramOffset + 2})`;
  }
}).join(', ');

// 收集所有参数值
const queryValues = batchData.flatMap(item => {
  if (item.id) {
    return [item.id, item.col1, item.col2];
  } else {
    return [item.col1, item.col2];
  }
});

// 组装Upsert语句
const upsertQuery = `INSERT INTO triage (id, col1, col2)
  VALUES ${valuePlaceholders}
  ON CONFLICT (id) DO UPDATE SET
    col1 = EXCLUDED.col1,
    col2 = EXCLUDED.col2`;

// 执行查询
try {
  await pool.query(upsertQuery, queryValues);
  res.status(201).json({ message: 'Updated' });
} catch (err) {
  await pool.query('ROLLBACK');
  console.log(JSON.stringify(err, null, 2));
}

方案2：使用NULL配合序列生成自增ID #

如果不想动态构造SQL，可以传入null作为无id行的参数，在SQL中用COALESCE结合表的自增序列生成id（BIGSERIAL的序列默认名为表名_id_seq）。

示例代码：

const upsertQuery = `INSERT INTO triage (id, col1, col2)
  VALUES (COALESCE($1, nextval('triage_id_seq')), $2, $3),
         (COALESCE($4, nextval('triage_id_seq')), $5, $6)
  ON CONFLICT (id) DO UPDATE SET
    col1 = EXCLUDED.col1,
    col2 = EXCLUDED.col2`;

// 无id的行传入null，而非'DEFAULT'
const values = [1, 'abc', 'def', null, 'ghi', 'jkl'];

try {
  await pool.query(upsertQuery, values);
  res.status(201).json({ message: 'Updated' });
} catch (err) {
  await pool.query('ROLLBACK');
  console.log(JSON.stringify(err, null, 2));
}

方案对比 #

• 方案1更灵活，无需依赖序列名称，适配动态变化的批量数据，推荐使用。
• 方案2适合固定数量的批量插入，但需要明确序列名称，若表名修改需同步调整SQL。

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