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