How to update multiple rows ?

[pullmann4rent]

Hello,

how can I update multiple rows ?

this is my current code

    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)

in pgp.as.format I have no plan id only when I map but I think I should not map all my pgp.as.format or how can I access it ?

[vitaly-t]

Again, see Column, which supports cnd flag, so you can have columns that are just for setting the condition. There are plenty of examples of it on StackOverflow. Here's just one example.

You would simply have one column name as ?plan_id or {name: 'plan_id', cnd: true}, and use it with WHERE clause.

See helpers.update.

[pullmann4rent]

Thank you very much! but I got this error:

error: column "location_id" is of type integer but expression is of type text

My location_id is INT NULL so it can be a integer or null, in my body I send location_id as null but I got this error above. How can I fix it ?
And if I remove location_id then I got error to the next column:

error: column "start_time" is of type timestamp without time zone but expression is of type text

[vitaly-t]

So apply type casting - attribute cast in the ColumnSet, according to expected types.

[pullmann4rent]

I did this:

         {
          name: 'start_time',
          mod: ':raw',
          init(c) {
              return pgp.as.format("to_timestamp($1)", c.value);
          }
        },

But I got error

error: invalid input syntax for type double precision: "2026-10-28 20:00:00"

[vitaly-t]

I suggested cast attribute, and you did something else entirely, now asking why it doesn't work :)

[vitaly-t]

What I suggested earlier, was {name: 'start_time', cast: 'timestamptz'}.

[vitaly-t]

I saw your question on StackOverflow, and I answered it.