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, Apr 20 '13 at 10:32
@mark A writable CTE, it let's you chain queries like this. Look here:http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/ It's 9.1+ feature though. — Jakub Kania, Apr 20 '13 at 11:33

score 13 · Accepted Answer · edited Sep 06 '17 at 19:42

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 06 '17 at 19:42

xav

5,452
7
48
57

answered Apr 20 '13 at 10:52

@mark: you need to tell the driver to return those values when you prepare the statement. See my edit. – Apr 20 '13 at 11:25
2

OK, that works. But something is left. You sound not sure whether the first row maps to the first returned id. The documentation at http://docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/preparedstatement.html 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? – mark Apr 20 '13 at 13:37
@mark: as I said: I don't know. You will need to test that for yourself. – Apr 20 '13 at 13:53
1

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. – mark Apr 20 '13 at 16:26
@mark: you could post that question to the Postgres JDBC mailing list http://www.postgresql.org/community/lists/ – Apr 20 '13 at 16:30
For Postgres db, the returned keys are in the order they are INSERTed. I have tested this both with Prepared and Non Prepared execution. – M-D Oct 28 '16 at 19:01
3

Please have a look at https://www.postgresql.org/message-id/flat/36689061-0844-2dfd-a4ee-8006d8a15ca9%40gmail.com , 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. – electrotype Dec 11 '20 at 14:51

Retrieving serial id from batch inserted rows in postgresql

1 Answers1

Linked