如何用pg-promise实现带子查询的多行插入？

你的核心问题是：pg-promise的helpers.insert会将子查询字符串自动转义为文本值，而非原生SQL执行。以下是两种可行的解决方式：

方式一：用pgp.raw()标记原生SQL值 #

直接在映射值时，将子查询用pgp.raw()包裹，告知pg-promise这是原生SQL无需转义，同时用占位符绑定参数避免注入：

var values = [{ start_time: '2023-05-01 11:15:00', end_time: '2023-05-01 17:35:00' },
              { start_time: '2023-01-01 19:20:00', end_time: '2023-01-02 23:35:00' }]
var machineId = 99;

const cs = new pgp.helpers.ColumnSet(['machine_id',
                                      'start_time',
                                      'end_time',
                                      'task_at_start',
                                      'task_at_end'],
                                    {table: 'events'});

// 映射值时用pgp.raw包裹子查询，用?作为参数占位符
const mappedValues = values.map((val) => ({
  machine_id: machineId,
  start_time: val.start_time,
  end_time: val.end_time,
  task_at_start: pgp.raw(`(select task_id from tasks where time = ?)`, [val.start_time]),
  task_at_end: pgp.raw(`(select task_id from tasks where time = ?)`, [val.end_time]) 
}));

// 注意：ON CONFLICT里的列名要和表结构一致，应该是machine_id而非machineId
const onConflict = `ON CONFLICT(machine_id, start_time) 
                    DO UPDATE SET end_time = COALESCE(events.end_time, EXCLUDED.end_time)`;                                  
const query = pgp.helpers.insert(mappedValues, cs) + onConflict;

方式二：在ColumnSet中定义列的初始化逻辑 #

将子查询逻辑放在ColumnSet的列定义里，让映射值更简洁：

var values = [{ start_time: '2023-05-01 11:15:00', end_time: '2023-05-01 17:35:00' },
              { start_time: '2023-01-01 19:20:00', end_time: '2023-01-02 23:35:00' }]
var machineId = 99;

// 在ColumnSet中为task_at_start/task_at_end定义init函数，动态生成子查询
const cs = new pgp.helpers.ColumnSet([
  {name: 'machine_id', def: machineId},
  'start_time',
  'end_time',
  {
    name: 'task_at_start',
    init: (col) => pgp.raw(`(select task_id from tasks where time = ?)`, [col.source.start_time])
  },
  {
    name: 'task_at_end',
    init: (col) => pgp.raw(`(select task_id from tasks where time = ?)`, [col.source.end_time])
  }
], {table: 'events'});

// 无需额外映射，直接使用原始values数组
const onConflict = `ON CONFLICT(machine_id, start_time) 
                    DO UPDATE SET end_time = COALESCE(events.end_time, EXCLUDED.end_time)`;                                  
const query = pgp.helpers.insert(values, cs) + onConflict;

关键注意点 #

1. 子查询必须用()包裹，确保SQL语法正确（单行子查询作为字段值时需要括号）。
2. 始终用占位符?绑定参数，不要直接拼接字符串，避免SQL注入风险。
3. 修正ON CONFLICT中的列名：你的表列是machine_id，而非machineId，原代码此处存在拼写错误，会导致冲突约束不生效。

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