Menu
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.
1000
node-postgres is a collection of node. js modules for interfacing with your PostgreSQL database. It has support for callbacks, promises, async/await, connection pooling, prepared statements, cursors, streaming results, C/C++ bindings, rich type parsing, and more!
To retrieve the last inserted ID with Node.js MySQL, we can get it from the callback that’s called when the query is done. connection.query ('INSERT INTO posts SET ?', { title: 'test' }, (err, result, fields) => { if (err) { throw err; } console.log (result.insertId); }); to call connection.query to make a INSERT query.
If the intended use case is to use the last inserted ID as part of the value of a subsequent insert, see this question. Recall that in postgresql there is no "id" concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type).
Recall that in postgresql there is no "id" concept for tables, just sequences (which are typically but not necessarily used as default values for surrogate primary keys, with the SERIAL pseudo-type). If you are interested in getting the id of a newly inserted row, there are several ways: Option 1: CURRVAL (<sequence name>);.
The following examples are equal to the examples from the previous page ( PHP Insert Data Into MySQL ), except that we have added one single line of code to retrieve the ID of the last inserted record. We also echo the last inserted ID: echo "New record created successfully. Last inserted ID is: " . $last_id;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
