Inserting Knex.js from selected
I am trying to create a request like the following via Knex.js:
INSERT INTO table` ("column1", "column2")
SELECT "someVal", 12345)
WHERE NOT EXISTS (
SELECT 1
FROM table
WHERE "column2" = 12345
)
Basically, I only want to insert values if the specific value doesn't exist yet. But Knex.js doesn't seem to know how to do this; if i call knex.insert()
(no values) it generates an "insert default values" query.
I tried the following:
pg.insert()
.into(tableName)
.select(_.values(data))
.whereNotExists(
pg.select(1)
.from(tableName)
.where(blah)
);
but that still gives me the default. I tried adding .columns(Object.keys(data))
in the hopes that it would insert()
accomplish this, but no luck.
Is it possible to generate the request I want using knex, or do I just need to create a raw request without Knex.js methods?
source to share
I believe the selection should be passed into the insert:
pg.insert(knex.select().from("tableNameToSelectDataFrom")).into("tableToInsertInto");
Also to select constant values or expressions, you need to use knex.raw expression in select:
knex.select(knex.raw("'someVal',12345")).from("tableName")
This is my first post and I haven't tested your specific example, but I have done similar things like what you are asking using the above methods.
source to share