Thank Lukas, I got around this by making everything typed using
DSL.val(Object value, Class<?> type) and then doing the insert with a List<Field<?>> rather than a List<Object> On Thursday, August 20, 2015 at 11:07:24 AM UTC-4, Lukas Eder wrote: > > Hello Max, > > Thank you for your enquiry. Indeed, there are some situations where we > need to cast bind values of value null in PostgreSQL as PostgreSQL is not > always able to infer the type only from the SQL syntax. I'm aware that this > isn't really the case for INSERT statements, but we currently don't > evaluate the bigger context in which we cast bind variables. Note that > PostgreSQL is pretty good at inferring types. There's much worse: > http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness > > We're aware of this situation and we're hoping to be able to fix this some > time soon. Relevant issues are: > > - https://github.com/jOOQ/jOOQ/issues/1735 > - https://github.com/jOOQ/jOOQ/issues/1749 > - https://github.com/jOOQ/jOOQ/issues/2823 > - https://github.com/jOOQ/jOOQ/issues/3398 > > Right now, there are several ways for you to work around this. For > instance: > > - You could provide the type other than Object > - You could use plain SQL: DSL.field("?", value) > > I hope this helps, > Lukas > > 2015-08-18 23:30 GMT+02:00 Max Kremer <[email protected] <javascript:>> > : > >> Hello fellow JOOQers >> >> >> I'm having an issue where the SQL being generated fails to execute >> because of type conversion. >> >> I have table with two columns A, B which are NUMERIC >> >> CREATE TABLE my_table ( >> id ....pk, >> A numeric(6,3), >> B numeric(6,4) >> ) >> >> >> I'm building my SQL query like this >> >> DSLContext d >> >> >> List<Object> values >> ... >> sql = d.insertInto(table(tableName)).columns(columnList); >> sql = sql.values(values); >> >> Note that the list of values are not typed (they are all Object) and some >> of them can be null. When a null value does happen to come up it creates an >> insert statement in SQL to this effect >> >> insert into my_table (id, A, B) >> values ($1, cast($2 as varchar), cast($3 as varchar)) >> >> >> >> Whats weird is it only attempts to cast when the values for A and B are >> null. How can I suppress the casting? >> >> -Max >> >> -- >> 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] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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.
