CODEWITHSUNDEEP
postgresqlknex.js
Sam Schick
Sam Schick

Reputation: 430

Insert with defaults using subquery in knex

I have this query;

knex('metrics').insert(function() {
  this.select('metric as name')
    .from('stage.metrics as s')
    .whereNotExists(function() {
      this.select('*')
        .from('metrics')
        .where('metrics.name', knex.raw('s.metric'))
    })
})

The table metrics has two columns; an id, which is incrementing, and name. I expected this to insert into the name column because the subquery has one column, labeled name, and default id. however, instead it complains that I am providing a column of type character varying for my integer column id. How do I make it explicit that I want the id to take the default value?

Upvotes: 0

Views: 2264

Answers (1)

coockoo
coockoo

Reputation: 2382

This can do the trick

knex('metrics').insert(function() {
    this
        .select([
            knex.raw('null::bigint as id'), // or any other type you need (to force using default value you need to pass explicitly null value to insert query)
            'metric as name'
        ])
        .from('stage.metrics as s')
        .whereNotExists(function() {
            this.select('*')
                .from('metrics')
                .where('metrics.name', knex.raw('s.metric'))
        })
})

I know, looks a bit hacky. Would be great to see something in knex API like (example below is a proposal and not a working example)

knex('table_name')
    .insert(
        ['name', 'surname'],
        function () {
            this.select(['name', 'surname']).from('other_table'))
        }
    )

Which produces

insert into table_name (name, surname) select name, surname from other_table;

I'm not sure about this interface, but you got the point. Like explicitly write fields you want to insert.

Upvotes: 2

Related Questions