0

I need to insert a lot of rows but only unique rows (it will take very long time to explain app architecture and why I can't make rows unique on Java side before inserting but in my case it's not an optimal way), my function is below:

private void addAttrNames(List<Attribute> attrs) throws SQLException {
    PreparedStatement stmt = getSession().prepareStatement(
            "INSERT INTO public.attr_names (\"name\") VALUES (?) ON CONFLICT DO NOTHING RETURNING id"
    );
    int index = 0;
    for (int i = 0; i < attrs.size(); ++i) {
        Attribute attr = attrs.get(i);
        stmt.setString(1, attr.getAttrName().getName());
        stmt.addBatch();
        if (i % BATCH_SIZE == 0 || i == attrs.size() - 1) {
            stmt.executeBatch();
            ResultSet keys = stmt.getGeneratedKeys();
            while (keys.next()) {
                attrs.get(index++).setAttrNameId(keys.getLong(1));
            }
            keys.close();
        }
    }
    stmt.close();
}

getSession().prepareStatement():

public PreparedStatement prepareStatement(String sql) throws SQLException {
    PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    statement.setFetchSize(FETCH_SIZE);
    return statement;
}

The problem is that keys is empty. I tried with and without RETURNING id part but it still doesn't work.

Is it possible to use batches with ON CONFLICT clause to return generated IDs?

P.S. I use PostgreSQL 11

Denis Sologub
  • 7,277
  • 11
  • 56
  • 123

0 Answers0