我在PostgreSQL中有一个upsert查询，比如:
INSERT INTO table
(id, name)
values
(1, 'Gabbar')
ON CONFLICT (id) DO UPDATE SET
name = 'Gabbar'
WHERE
table.id = 1
我需要用knex来完成这个向上插入的查询.怎么办？
我在PostgreSQL中有一个upsert查询，比如:
INSERT INTO table
(id, name)
values
(1, 'Gabbar')
ON CONFLICT (id) DO UPDATE SET
name = 'Gabbar'
WHERE
table.id = 1
我需要用knex来完成这个向上插入的查询.怎么办？
所以我用Dotnil's answer on Knex Issues Page的建议解决了这个问题:
var data = {id: 1, name: 'Gabbar'};
var insert = knex('table').insert(data);
var dataClone = {id: 1, name: 'Gabbar'};
delete dataClone.id;
var update = knex('table').update(dataClone).whereRaw('table.id = ' + data.id);
var query = `${ insert.toString() } ON CONFLICT (id) DO UPDATE SET ${ update.toString().replace(/^update\s.*\sset\s/i, '') }`;
return knex.raw(query)
.then(function(dbRes){
// stuff
});
希望这对别人有帮助.