How to get the serial id just after inserting a row?

Question

I have a table with row 'id' (a primary key) default set to serial in PostgreSQL. I insert into this row by calling

session.getCurrentSession().createSQLQuery("some insert query")

without adding any value into id as it is default set to serial.

How can I retrieve the `id' of just inserted row?

Did you search? The above article was my first hit for a search on "postgresql jdbc generated keys". — Craig Ringer, Jun 03 '13 at 13:50

score 5 · Answer 1 · answered Jun 03 '13 at 19:57

JDBC statements can return the generated keys. For instance, if the table has a single column id of type serial (probably PK) that is not mentioned in the insert SQL below, the generated value for this column can be obtained as:

PreparedStatement s = connection.createStatement
  ("INSERT INTO my_table (c,d) VALUES (1,2)", 
    Statement.RETURN_GENERATED_KEYS);
s.executeUpdate(); 
ResultSet keys = s.getGeneratedKeys();
int id = keys.getInt(1);

This is faster than sending the second query to obtain the sequence value or max column value later. Also depending on circumstances these two other solutions may not be not be thread safe.

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

Since it is serial you can use select max(id) from tableName

     Using max(id) is a very bad idea. It will not give you the correct result 
    in case of multiple concurrent transactions. The only correct way is to use 
curval() or the returning clause.

In posgresql: There is already a stackoverflow-question exists BTW.

   `INSERT INTO tableName(id, name) VALUES(DEFAULT, 'bob') RETURNING id;`

(also)

Get a specific sequence:

SELECT currval('name_of_your_sequence');

Get the last value from the last sequence used:

SELECT lastval();

Manual: http://www.postgresql.org/docs/current/static/functions-sequence.html

For PHP-mysql users:

From php.net clickhere

<?php
$link = mysqli_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysqli::$connect_error() );
}
mysqli::select_db('mydb');

mysqli::query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysqli::$insert_id());
?>

But you need to connect for every query.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 03 '13 at 12:46

pinkpanther

4,770
2
38
62

Actually the code i am writing is targeted for large number of users.. in that case it is possible that multiple insertions may take place within milisecond – Kaushik Shrestha Jun 03 '13 at 12:51
appreciate your effort, but i am not using mysql db with php but rather Postgres with hibernate framework (in java) – Kaushik Shrestha Jun 03 '13 at 13:22
2

using `max(id)` is a **very** bad idea. It will ***not*** give you the correct result in case of multiple concurrent transactions. The **only** correct way is to use `curval()` or the `returning` clause. – Jun 03 '13 at 20:43
@a_horse_with_no_name +1 that's why I used the `mysqli::$insert_id()` in the latter example...I have edited to emphasize this thanks :) – pinkpanther Jun 04 '13 at 04:24

score 0 · Answer 3 · edited May 23 '17 at 11:49

0

use SELECT CURRVAL(); . Typically used in conjunction with pg_get_serial_sequence

postgreSQL function for last inserted ID

edited May 23 '17 at 11:49

Community

1
1

answered Jun 03 '13 at 12:47

This is only useful if you're only inserting one row at a time, though. – Craig Ringer Jun 03 '13 at 13:48

How to get the serial id just after inserting a row?

3 Answers3

Linked