Usage of 'skip' option in multi-row update

Question

I am new to NodeJs and tried to use pg-promise to do all the requests to my PG database. I want to be able to update columns dynamically, meaning sometimes I will update only two columns for a row, sometimes I will update all of them, etc ... My input will be a JSON.

Since I want the endpoint to be able to update multiple rows, I tried using helpers namespace with ColumnSet.

Here is my Javascript code (inspired from previous stackoverflow answers):

  /* logic for skipping columns: */
  const skip = c => !c.exists || c.value === undefined;
  
  /* all the columns of the table */
  const column_structure = new dbconfig.pgp.helpers.ColumnSet(
  [ '?id',
    {name: 'firstname', skip}, 
    {name: 'surname', skip}, 
    {name: 'yob', skip}, // year of birth
    {name: 'defensive_skill', skip}, 
    {name: 'offensive_skill', skip}, 
    {name: 'login', skip}, 
    {name: 'password', skip}
  ],
     {table: 'players'});

Here is the JSON I am feeding to the endpoint :

[{
  "id" : 25,
  "firstname": "Stephen",
  "surname": "Harrison",
  "yob": 1991,
  "defensive_skill": 5,
  "offensive_skill": 3,
  "login": "harry",
  "password": "123456"
},
{
  "id": 26,
  "firstname": "Chris",
  "surname": "Jackson",
  "defensive_skill": 5,
  "offensive_skill": 4,
  "login": "chris",
  "password": "123456"
}
]

And here is the error :

Property 'yob' doesn't exist.

As you can see, in the second object of my array, I didn't specify the field 'yob'. I was expecting that for the second object, all the columns will be updated except the 'yob'. Is there something that I am doing wrong?

Answer 1

The reason it doesn't work, is because skip logic is only possible for single-row updates, as documented in the API:

Used by methods update (for a single object)...

Multi-row update syntax doesn't allow for any skipping logic, and so you need to provide a default value when the property is missing, like this:

{name: 'yob', skip, def: defaultValue}

defaultValue can be anything above, including undefined.

Alternatively, you can make use of property init, and return a value dynamically.

---

So in the code above, if you change the column declaration to this:

{name: 'yob', skip, def: null}

your update call will generate:

UPDATE "players" AS t SET "firstname"=v."firstname","surname"=v."surname","yob"=v."yob","defensive_skill"=v."defensive_skill","offensive_skill"=v."offensive_skill","login"=v."login","password"=v."password" FROM (VALUES(25,'Stephen','Harrison',1991,5,3,'harry','123456'),(26,'Chr
is','Jackson',null,5,4,'chris','123456')) AS v("id","firstname","surname","yob","defensive_skill","offensive_skill","login","password")

As you can see from the generated SQL, it is impossible to skip one column with such syntax, that's why skip is ignored for multi-row updates. You can see it however working, if you pass in one object at a time, but that's a different usage scenario than the one you are looking for.