I'm doing a batch update in Postgres using Knex.js. I don't know in advance which of the rows were actually changed.
The update is wrapped in a transaction along with an insert operation to create a customized "upsert" operation. It looks more or less like this:
const upsertData = (data, trx) => {
// Generate an update query for each row if row exists
const updateQueries = [];
data.forEach(row => {
const query = trx('data')
.returning('id')
.where('id', '=', row.id)
.update(row)
.transacting(trx); // This makes every update be in the same transaction
updateQueries.push(query);
})
return Promise.all(updateQueries) // Once every query is written
.then(existingIds => {
// Filter out the remaining rows
const flatIds = existingIds.reduce((acc, id) => acc.concat(id), []);
const rowsToInsert = data.filter(row => !flatIds.includes(row.id))
// And insert them
if (rowsToInsert.length > 0) {
return trx('data')
.returning('Title')
.insert(rowsToInsert)
}
})
.then(() => console.log('Data updated'))
.catch(err => {
console.log('error updating data: ', err);
throw 'data update error';
});
}
This way I get the ids of all updated rows, so I can insert the rest of the data. But now I also want to tell which of the updating rows were actually changed. That is, a lot of the rows were updated with the exact same data (as mentioned above, I have no way of telling in advance which rows have changed. I would have to read the existing data and compare it with the new data coming in). I want to tell exactly which rows received new data and which didn't.
Is this at all possible? If so, how?
Any help would be greatly appreciated!