When issuing an "insert" statement to postgres, how can I get the ID of the row last inserted in the DB?
I tried "pg", "pg-native", "pg-connection", and a bunch of other packages. For each package, I tried the sync and async method. I read the thing that pretends to be package documentation. I checked the source code for the packages. For the life of me I can't figure this out, and I can't believe that I'm the only person to face this issue.
Any insight would be greatly appreciated.
The key here is using RETURNING id in your INSERT query.
The pg wiki has an example that shows how this is done.
// Let's pretend we have a user table with the 'id' as the auto-incrementing primary key
var queryText = 'INSERT INTO users(password_hash, email) VALUES($1, $2) RETURNING id'
client.query(queryText, ['841l14yah', '[email protected]'], function(err, result) {
if (err) //handle error
else {
var newlyCreatedUserId = result.rows[0].id;
}
});
or with async/await:
const result = await client.query(queryText, ['841l14yah', '[email protected]']);
const newlyCreatedUserId = result.rows[0].id;
RETURNING id part, so I edited the answer to show this.