Retrieving serial id from batch inserted rows in postgresql

Question

Here is the code that works:

        Connection c = ds.getConnection();
        c.setAutoCommit(false);
        PreparedStatement stmt = c.prepareStatement("INSERT INTO items (name, description) VALUES(?, ?)");
        while (!(items = bus.take()).isEmpty()) {
          for (Item item : items) {
            stmt.setString(1, item.name);
            stmt.setString(2, item.description);
            stmt.addBatch();
          }
          stmt.executeBatch();
          c.commit();
        }

But now I need to populate another table where id is a foreign key. If I use INSERT with RETURNING id then executeBatch fails with "A result was returned when none was expected" error.

I see several ways to solve this

Do individual insert rather than the batch insert.
Replace serial id with client generated guid.
Use some kind of a stored procedure to perform the batch insert and return a list of ids.

Of the three methods that I see the last one seems to preserve both the efficiency of batch insert and return the ids, but it is also the most complex for me as I have never written stored procedures.

Is there a better way to batch insert and get the IDs? I have no problem using postgresql specific API rather than jdbc.

If not, could any one sketch such a stored procedure?

Here is the table schema:

CREATE UNLOGGED TABLE items
(
  id serial,
  name character varying(1000),
  description character varying(10000)
)
WITH (
  OIDS=FALSE
);

Take a look at the JDBC getGeneratedKeys method. I haven't specifically verified it for batch connections, though. — Craig Ringer
– Craig Ringer, Commented Apr 20, 2013 at 10:32
@mark A writable CTE, it let's you chain queries like this. Look here:depesz.com/2011/03/16/waiting-for-9-1-writable-cte It's 9.1+ feature though. — Jakub Kania
– Jakub Kania, Commented Apr 20, 2013 at 11:33

xav · Accepted Answer · 2017-09-06 19:42:35Z

13

Something like this should work:

// tell the driver you want the generated keys
stmt =  c.prepareStatement("INSERT ... ", Statement.RETURN_GENERATED_KEYS);

stmt.executeBatch();

// now retrieve the generated keys
ResultSet rs = stmt.getGeneratedKeys();
while (rs.next()) {
 int id = rs.getInt(1);
 .. save the id somewhere or update the items list 
}

I think (I am not sure!) that the keys are returned in the order they were generated. So the first row from the ResultSet should map to the first "item" from the list you are processing. But do verify that!

Edit

If that doesn't work, try specifying the actual columns for which the values are generated:

stmt =  c.prepareStatement("INSERT ... ", new String[] {"id"});

edited Sep 6, 2017 at 19:42

xav

5,6387 gold badges53 silver badges59 bronze badges

answered Apr 20, 2013 at 10:52

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

user330315 Over a year ago

@mark: you need to tell the driver to return those values when you prepare the statement. See my edit.

mark Over a year ago

OK, that works. But something is left. You sound not sure whether the first row maps to the first returned id. The documentation at docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/… says nothing about this too. However, this is a crucial piece of information. Without this guarantee the feature is useless. Is it guaranteed, at least for postgresql?

user330315 Over a year ago

@mark: as I said: I don't know. You will need to test that for yourself.

mark Over a year ago

I tested it, but it proves nothing. The fact that it works in my dev environment does not say much about whether it is going to work in production. We need some authoritive answer from a postgresql guru. But I will credit your reply as the answer.

electrotype Over a year ago

Please have a look at postgresql.org/message-id/flat/… , I asked the question on the mailing list. It seems the order is clearly not guaranteed at this time. You are playing with fire by basing some code on the expectation that it is.

|

Collectives™ on Stack Overflow

Retrieving serial id from batch inserted rows in postgresql

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related