How to get the Postgresql serial field value after an INSERT

Assuming you are using Postgres (Postgresql), and:

  • you have *just* done an INSERT into a table named order,
  • the name of the sequence for that table is order_order_id_seq
  • you are using the Java programming language
  • you are still using the same database connection that you used with your INSERT statement

to get the value of the Postgres serial value just created after your INSERT statement, use some code like this:

  // assume we have a database connection named "connection"
  int serialNum = 0;
  Statement stmt = connection.createStatement();

  // get the postgresql serial field value with this query
  String query = "select currval('order_order_id_seq')";
  ResultSet rs = stmt.executeQuery(query);
  if ( rs.next() )
  {
    serialNum = rs.getInt(1);
    System.out.println( "serialNum = " + serialNum );
  }
  stmt.close();
  connection.close();

Note that you can also test this from the Postgresql command line (psql) by first performing an INSERT statement, and then following it with this same SQL SELECT statement.

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.