Return Unaffected Rows in Postgres

Question

I have a Postgres DB and using ObjectionJS as my ORM.

I have the following ObjectionJS code to patch two columns of a user_system table.

  public static async DeleteSystem(systemId: number, context: Context): Promise<UserSystem> {
    const system = await UserSystem.query().context(context).patch({
      state: PointInTimeState.inactive,
      receiveNotifications: false
    }).where({
      system_id: systemId
    }).throwIfNotFound().debug()
    //("as any" for now because the system variable is a number at this point (i.e NOT of type Promise<UserSystem> as in the signature)
    return system as any 
  }

Questions

Is there a way in which I could return all the Rows that were not affected by this patch?
If so, how, without having to write two separate queries (to update and then requery new data) to the back end ?

Well perhaps you could create a CTE which does the update and returns the ctid of each row updated. The select, completing the query, then retrieves the rows where the ctid is not in the returned list. Just the idea/technique as I have no idea how to accomplish it in your ORM. — Belayer, Dec 17 '19 at 23:19

score 0 · Answer 1 · answered Dec 18 '19 at 23:47

As far as I know writing CTE combining two queries to single is the only way.

With objection / knex they can be done with https://knexjs.org/#Builder-with

So in with query you do the .patch(...).where(...).returning('id') and in main query you select all rows from the table, which are not in id set returned by the first query.

Something like this (not sure if this works at all like this with objection):

UserSystem.query().context(context).with('patchedIds', 
    UserSystem.query().patch({
      state: PointInTimeState.inactive,
      receiveNotifications: false
    }).where({
      system_id: systemId
    }).returning('id') 
  ).whereNotIn('id', builder => {
    builder.from('patchedId').select('id')
  })

Return Unaffected Rows in Postgres

1 Answers1