I have a collection of tuples that need to be inserted into a table as new records.I was trying to create a List of TableRecord objects built off of these Tuples via MYTABLE.newRecord() and then insert them with batchInsert(), but the fields I am not setting are getting sent to Postgres 9.6 as NULL instead of DEFAULT which causes a constraint violation.
I tried calling record.setValue(MYTABLE.ACOLUMN, DSL.defaultValue(MYTABLE.ACOLUMN)), but that gives a compile error that the type for T can't be determined.
I am currently trying to switch to using insertInto().columns(<just the ones I have>) and then looping over the records to build a series of .values() calls, but it seems like I have to pull the values out of the records manually since .values() doesn't have a form that takes a TableRecord object.
I have a collection of tuples that need to be inserted into a table as new records.I was trying to create a List of TableRecord objects built off of these Tuples via MYTABLE.newRecord() and then insert them with batchInsert(), but the fields I am not setting are getting sent to Postgres 9.6 as NULL instead of DEFAULT which causes a constraint violation.Interesting, how are you "not setting" them, I.e. after creating the record, do you set the values individually, or do you load the record from a POJO using Record.from(Object)?
Note that with a batch insertion, since the desired behaviour is to have only a single JDBC statement, all the values are set explicitly. If you wanted DEFAULT behaviour, then it would be quite likely that each insertion would generate a new JDBC statement, and thus batching is useless compared to individual insertions.I wonder if we should go through the argument list and check if at least one record has its changed() flag set to true though. Perhaps, if all the records are consistent with respect to changed() flags, we could indeed have DEFAULT behaviour across the batch. I've created a feature request for this:
I tried calling record.setValue(MYTABLE.ACOLUMN, DSL.defaultValue(MYTABLE.ACOLUMN)), but that gives a compile error that the type for T can't be determined.This doesn't work indeed, because currently, only actual values are allowed as record values, not Field expressions.Allowing field expressions would break quite a bit of API, including record.get(MYTABLE.ACOLUMN), which isn't able to return expressions. Also the changed flag would always need to be set to true, because the expression might be non-deterministic and would have to be evaluated on each INSERT / UPDATE...Sounds too tricky to support.
I am currently trying to switch to using insertInto().columns(<just the ones I have>) and then looping over the records to build a series of .values() calls, but it seems like I have to pull the values out of the records manually since .values() doesn't have a form that takes a TableRecord object.No, values() doesn't but insertInto(TABLE).set(record) is certainly possible. And then, you can call newRecord() to add another record to the bulk insert statement.
I did notice the .set() variant, but because the JavaDoc or the manual specifically mentioned that it came out of MySQL, I wasn't sure if it would work as desired for Postgres.
So if I used:stmt = insertInto(TABLE).columns(X,Y,Z);records.forEach(record -> stmt.set(record));stmt.execute();
Would it pick out only the columns that I specified from the collection of records that was passed in?
Is it any improvement over the same code with stmt.values(r.getX(), r.getY(), r.getZ()) instead?