How to update multiple rows with pg promise?

Question

I want to update multiple rows with pg promise but I don’t understand it fully

Here is my code

I don’t know where I have access to my plan_id

    const cs = new pgp.helpers.ColumnSet(
      ['user_id', 'tenant_id', 'location_id', 'start_time', 'end_time', 'time_range', 'status', 'info'], { table: 'workers_plan' });

    const cs_values: UpdateWorkersPlan[] = plan.map((el: UpdateWorkersPlan) => ({
      plan_id: el.plan_id,
      user_id: el.user_id,
      tenant_id,
      location_id: el.location_id,
      start_time: el.start_time,
      end_time: el.end_time,
      time_range: tsrange(el.start_time, el.end_time),
      status: el.status,
      info: el.info,
    }));

    pgp.as.format('WHERE plan_id = $1', [how to access plan id ?])

    await pgp.helpers.update(cs_values, cs)

Answer 1

With my experience, to update multiple rows with pg-promise, you can utilize pgp.helpers.update method.

So you should include plan_id in ColumnSet definition and map it in cs_values:

(haven't tested this code)

const cs = new pgp.helpers.ColumnSet(
  ['plan_id', 'user_id', 'tenant_id', 'location_id', 'start_time', 'end_time', 'time_range', 'status', 'info'], { table: 'workers_plan' });

const cs_values = plan.map((el) => ({
  plan_id: el.plan_id,
  user_id: el.user_id,
  tenant_id,
  location_id: el.location_id,
  start_time: el.start_time,
  end_time: el.end_time,
  time_range: tsrange(el.start_time, el.end_time),
  status: el.status,
  info: el.info,
}));

await pgp.helpers.update(cs_values, cs);

Docs: https://vitaly-t.github.io/pg-promise/helpers.html

Answer 2

You need to make use of the conditional column, defined either as ?plan_id or as {name: 'plan_id', cnd: true}, and append WHERE clause that uses that column. See update for examples.

const cs = new pgp.helpers.ColumnSet(
  [
     '?plan_id',
     'user_id',
     'tenant_id',
     'location_id',
     'start_time',
     'end_time',
     {
         name: 'time_range',
         mod: ':raw',
         init(c) {
             return pgp.as.format('tsrange(${start_time}, ${end_time})', c.source);
         }
     }
     'status',
     'info'
], { table: 'workers_plan' });

const query = pgp.helpers.update(plan, cs) + ' WHERE v.plan_id = t.plan_id';

await db.none(query);

And you do not need to remap data, as shown here, use the plan array directly, as ColumnSet is better and more flexible at remapping data.