PG-Promise - "Property doesn't exist" error with skip function

Question

I am having trouble figuring out how to utilize skip to skip undefined/null values. I keep getting Error: Property 'vehicle_id' doesn't exist. Is skip within column set and skip of upsertReplaceQuery() somehow conflicting with each other? How can I get it to work?

const vehicleColumnSet = new pgp.helpers.ColumnSet(
  [
    { name: 'user_id' },
    {
      name: 'vehicle_id',
      skip: (c) => !c.exists,
    },
    { name: 'model_id', def: null },
  ],
  { table: 'vehicle' }
);

const upsertReplaceQuery = (data, columnSet, conflictField) => {
  return `${pgp.helpers.insert(
    data,
    columnSet
  )} ON CONFLICT(${conflictField}) DO UPDATE SET ${columnSet.assignColumns({
    from: 'EXCLUDED',
    skip: conflictField,
  })}`;
};

const vehicleUpsertQuery = upsertReplaceQuery(
  {
    user_id,
    model_id: vehicle_model,
  },
  vehicleColumnSet,
  'user_id'
);

await task.none(vehicleUpsertQuery);

score 1 · Accepted Answer · answered Mar 18 '21 at 19:07

PostgreSQL has no support for any skip logic within its multi-row insert syntax.

And pg-promise documentation also tells you within skip description:

An override for skipping columns dynamically. Used by methods update (for a single object) and sets, ignored by methods insert and values. It is also ignored when conditional flag cnd is set.

At most, you can add such logic against a single-row insert, as shown here.

PG-Promise - "Property doesn't exist" error with skip function

1 Answers1