knex.js中的查询除外

最快的检查方式 existence 在postgres中是 where exists/not exists

这很容易做到 knex

const knex = require('knex')({ client: 'pg' })

const builder = knex('table_name')
    .insert((sub) => {
        sub.select('*').from('another_table').whereNotExists((existsBuilder) => {
            existsBuilder
                .select('*')
                .from('table_name')
                .whereRaw('table_name.id = another_table.id')
        })
    })

console.log(builder.toString())
// => insert into "table_name" select * from "another_table" where not exists (select * from "table_name" where table_name.id = another_table.id)

通常,postgresql会优化您的 not in (subquery) except 陈述

Knex没有任何特殊支持 EXCEPT 查询,因此如果您喜欢使用它,那么您确实需要执行以下操作:

knex.raw(`? EXCEPT ?`, [query1, query2])