Thank you for the reply. I am successfully using a series of explicitly set .value() calls now, but here are some follow-up comments in-line.
On Monday, May 29, 2017 at 11:38:42 AM UTC-4, Lukas Eder wrote: > 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)? > After the .newRecord(), I was just explicitly calling .setXXX() on the fields I was interested in and did not touch the ones I wanted to be default. There was no loading of the data from a POJO. I did try calling .reset(Field) on the fields to see if it might help. > 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: > https://github.com/jOOQ/jOOQ/issues/6294 > > This sounds like it would probably work well for my use case. > 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. > > That is fine. I think I was just looking in the wrong area for a solution here. > 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? -Daniel > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
