How to get a value from the last inserted row?

Question

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.

Answer 1

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

currval(sequence)

Quote:

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.

Answer 2

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())

Answer 3

PostgresSQL - RETURNING

INSERT INTO mytable( field_1, field_2,... ) VALUES ( value_1, value_2 ) RETURNING anyfield

It will return the value of "anyfield". "anyfield" may be a sequence or not.

Hope it helps

Answer 4

See the API docs for java.sql.Statement.

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();

Answer 5

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 : https://www.ibm.com/developerworks/java/library/j-jdbcnew/

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);
}

Answer 6

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));
  }

Answer 7

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

Answer 8

Since PostgreSQL JDBC driver version 8.4-701 the PreparedStatement#getGeneratedKeys() 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 (which is at the moment still 8.4-701).