Multi-row insert with subqueries using pg-promise

Question

My Node.js app is currently saving events in a table using multi-row insert:

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([ {name: 'machine_id', def: machineId },
                                        'start_time',
                                        'end_time'}],
                                      {table: 'events'});
const onConflict = `ON CONFLICT(machineId, start_time) 
                          DO UPDATE SET end_time = COALESCE(events.end_time, EXCLUDED.end_time)`;                                  
const query = pgp.helpers.insert(values, cs) + onConflict;

Now, I would like to expand the events table to include two more columns of data to provide details about the events. These data is retrieved from a different table and it would be great to be able to add it in the same insert query.

I have tried the following:

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'});
const values = values.map((val) => ({
                                   machine_id: machineId,
                                   start_time: val.start_time,
                                   end_time: val.end_time,
                                   task_at_start: pgp.as.format('select task_id from tasks where time = $1', [val.start_time]),
                                   task_at_end: pgp.as.format('select task_id from tasks where time = $1', [val.end_time]) 
}))

const onConflict = `ON CONFLICT(machineId, start_time) 
                              DO UPDATE SET end_time = COALESCE(events.end_time, EXCLUDED.end_time)`;                                  
const query = pgp.helpers.insert(values, cs) + onConflict;

This tries to insert the subqueries in columns task_at_start and task_at_end as text.

Is there anyway to insert do a multi-row insert that includes a subquery like this?

I have already commented on this related question and I understand that it may not be possible to do what I intend to but I have decided to create a question on my own to fully detail my case.

Answer 1

Syntax for Column is well documented. So, it would be something like this:

const 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'}]

const machineId = 99;

const cs = new pgp.helpers.ColumnSet(
    [
        {
            name: 'machine_id',
            prop: 'machineId',
            init: () => machineId
        },
        'start_time',
        'end_time',
        {
            name: 'task_at_start',
            init: c => pgp.as.format('select task_id from tasks where time = ${start_time}', c.source),
            mod: ':raw'
        },
        {
            name: 'task_at_end',
            init: c => pgp.as.format('select task_id from tasks where time = ${end_time}', c.source),
            mod: ':raw'
        }
    ],
    {table: 'events'});

const onConflict = `ON CONFLICT(machineId, start_time) 
                              DO UPDATE SET end_time = COALESCE(events.end_time, EXCLUDED.end_time)`;

const query = pgp.helpers.insert(values, cs) + onConflict;

In general, it is not a good solution what you are doing here, because you will end up executing two SELECT queries for every row being inserted.

P.S. There is no need for extra data re-mapping logic here.