A way to get the id of inserted row as callback?

[johhansantana]

For inserting a row, the callback just sends back this:

{
  "command": "INSERT",
  "rowCount": 1,
  "oid": 0,
  "rows": [],
  "fields": [],
  "_parsers": [],
  "RowCtor": null,
  "rowAsArray": false
}

Is there a way to get the id of the inserted row?

[brianc]

INSERT INTO foo (name) VALUES ('baz') RETURNING *

[jmarca]

Got to this the long way. Can you update the docs (specifically at https://node-postgres.com/features/queries#parameterized-query) ---to use this syntax?

I've used RETURNING before when doing a data-modifying WITH clause, but I didn't think to add it to a plain insert.

(also, my way of using it is with async/await...)

Suggestion:

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', '[email protected]']

// callback
client.query(text, values, (err, res) => {
  if (err) {
    console.log(err.stack)
  } else {
    console.log(res.rows[0])
  }
})

// promise
client.query(text, values)
  .then(res => console.log(res.rows[0]))
  .catch(e => console.error(e.stack))

// async/await, pool

try {
    const pool = new Pool()
    const {rows} = await pool.query(text, values)
    console.log(rows[0])
}catch(e){
   console.error(e)
   setImmediate(() => { throw e })
}

[charmander]

You don’t always need RETURNING and it’s something of a well-known PostgreSQL feature, so I’m not sure it has to be in driver docs.

[brianc]

@jmarca I agree you don't always need a RETURNING on an insert, but I added a bit more docs based on your example since it couldn't hurt. 😄

https://node-postgres.com/features/queries#parameterized-query

[jmarca]

Yes of course you don't always need "returning", and postgresql inserts typically just return the sparse phrase INSERT 0 1 or whatnot. But its been a while, and the way the docs were written, I interpreted the res => console.log(res.rows[0]) as implying that res.rows[0] would contain the results of the insert. Of course it doesn't unless you ask for it, but I'd forgotten that.

That's the trouble with switching between different languages and databases...one remembers the broad stokes but forgets the details.

Thanks for the addition to the docs

[thdomingues-dev]

I'm using KnexJS and have the same problem, solve using "returning"

just like this: // Returns [1]
knex('books')
.returning('id')
.insert({title: 'Slaughterhouse Five'})