How to get a value from the last inserted row?

Is there some way to get a value from the last inserted row?

I am inserting a row where the PK will automatically increase, and I would like to get this PK. Only the PK is guaranteed to be unique in the table.

I am using Java with a JDBC and PostgreSQL.

Use sequences in postgres for id columns:

INSERT mytable(myid) VALUES (nextval('MySequence'));

SELECT currval('MySequence');

currval will return the current value of the sequence in the same session.

(In MS SQL, you would use @@identity or SCOPE_IDENTITY())

See the API docs for [java.sql.Statement][1].

Basically, when you call `executeUpdate()` or `executeQuery()`, use the `Statement.RETURN_GENERATED_KEYS` constant. You can then call `getGeneratedKeys` to get the auto-generated keys of all rows created by that execution. (Assuming your JDBC driver provides it.)

It goes something along the lines of this:

Statement stmt = conn.createStatement();
stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet keyset = stmt.getGeneratedKeys();

[1]:

[To see links please register here]

If you're using JDBC 3.0, then you can get the value of the PK as soon as you inserted it.

Here's an article that talks about how :

[To see links please register here]

Statement stmt = conn.createStatement();
// Obtain the generated key that results from the query.
stmt.executeUpdate("INSERT INTO authors " +
"(first_name, last_name) " +
"VALUES ('George', 'Orwell')",
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if ( rs.next() ) {
// Retrieve the auto generated key(s).
int key = rs.getInt(1);
}

Here is how I solved it, based on the answers here:

Connection conn = ConnectToDB(); //ConnectToDB establishes a connection to the database.
String sql = "INSERT INTO \"TableName\"" +
"(\"Column1\", \"Column2\",\"Column3\",\"Column4\")" +
"VALUES ('value1',value2, 'value3', 'value4') RETURNING
\"TableName\".\"TableId\"";
PreparedStatement prpState = conn.prepareStatement(sql);
ResultSet rs = prpState.executeQuery();
if(rs.next()){
System.out.println(rs.getInt(1));
}

Don't use SELECT currval('MySequence') - the value gets incremented on inserts that fail.

The sequences in postgresql are transaction safe. So you can use the

currval(sequence)

[Quote:][1]

> currval
>
> >Return the value most recently obtained by nextval for this sequence
> in the current session. (An error is
> reported if nextval has never been
> called for this sequence in this
> session.) Notice that because this is
> returning a session-local value, it
> gives a predictable answer even if
> other sessions are executing nextval
> meanwhile.


[1]:

[To see links please register here]

Since PostgreSQL JDBC driver version [8.4-701][1] the [`PreparedStatement#getGeneratedKeys()`][2] is finally fully functional. We use it here almost one year in production to our full satisfaction.

In "plain JDBC" the `PreparedStatement` needs to be created as follows to make it to return the keys:

statement = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);

You can download the current JDBC driver version [here][3] (which is at the moment still 8.4-701).


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

For MyBatis 3.0.4 with Annotations and Postgresql driver 9.0-801.jdbc4 you define an interface method in your Mapper like

public interface ObjectiveMapper {

@Select("insert into objectives" +
" (code,title,description) values" +
" (#{code}, #{title}, #{description}) returning id")
int insert(Objective anObjective);

Note that @Select is used instead of @Insert.

PreparedStatement stmt = getConnection(PROJECTDB + 2)
.prepareStatement("INSERT INTO fonts (font_size) VALUES(?) RETURNING fonts.*");
stmt.setString(1, "986");
ResultSet res = stmt.executeQuery();
while (res.next()) {
System.out.println("Generated key: " + res.getLong(1));
System.out.println("Generated key: " + res.getInt(2));
System.out.println("Generated key: " + res.getInt(3));
}
stmt.close();