Inserting NaN values into postgresql database with JOOQ

Question

How can I insert Double.NaN into a PostgreSQL 'double precision' field with JOOQ?

I'm using PostgreSQL 9.5 with JOOQ 3.7.3. I'm inserting sensor values in batch into a table. Sometimes the value is a NaN, and when JOOQ tries to insert it, it throws an exception.

org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [];
nested exception is java.sql.BatchUpdateException: Batch entry 6 
insert into "sensordata" ("measure_time", "location_id", "sensor_id", "value") 
values (1462551000, 12, 15, NaN)
on conflict ("measure_time", "location_id", "sensor_id")
do update set "value" = NaN 
was aborted.  
Call getNextException to see the cause.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) ~[spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]

The value field is defined in SQL as such: "value" DOUBLE PRECISION NOT NULL DEFAULT 0.0

The relevant part of my Java code:

final List<InsertOnDuplicateSetMoreStep<SensordataRecord>> inserts =
seq(list)
.map(this::process)
.flatMap(t -> t)
.map(d ->
    sql.insertInto(SENSORDATA, SENSORDATA.MEASURE_TIME, SENSORDATA.LOCATION_ID, SENSORDATA.SENSOR_ID, SENSORDATA.VALUE)
       .values(d.getMeasureTime(), d.getLocationId(), d.getSensorId(), d.getValue())
       .onDuplicateKeyUpdate()
       .set(SENSORDATA.VALUE, d.getValue())
    )
    .toList();
 sql.transaction(cfg -> DSL.using(cfg).batch(inserts).execute());

The problem is with the batch insert, after I replaced it with inserts.forEach(query -> DSL.using(cfg).execute(query)); the transaction runs just fine. Maybe I should change the question to: "Why does batch insert fail when using the onDuplicateKeyUpdate clause?", but I'm not sure if that is the real problem with my query. — kazmer
– kazmer, Commented May 6, 2016 at 17:57

Lukas Eder · Accepted Answer · 2016-05-09 13:45:37Z

0

This is a bug in jOOQ: https://github.com/jOOQ/jOOQ/issues/5249

jOOQ should explicitly cast NaN to double precision as such: 'NaN'::double precision. There are two possible workarounds for you:

Implement that cast yourself in the client, by implementing explicit NaN checks
Avoid batching of multiple statements by using the single-statement (multiple bind variable) batch as documented here: http://www.jooq.org/doc/latest/manual/sql-execution/batch-execution
Use jOOQ's Loader API for your data insertion as documented here: http://www.jooq.org/doc/latest/manual/sql-execution/importing

In this particular case, the latter is probably preferrable as you will get much better performance out of it by being able to fine-tune bulk, batch, and commit sizes.

answered May 9, 2016 at 13:45

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kazmer Over a year ago

It looks like the only option is to implement the casting with explicit NaN check. The single statement with multiple bind variables doesn't work, because I need the onDuplicateKeyUpdate clause too.

Collectives™ on Stack Overflow

Inserting NaN values into postgresql database with JOOQ

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related