Error on NaN literal in SQL statement

[Silvio]

Hello,

Inserting Nan values in a DOUBLE column seems to work when the insert/merge statement holds a ? placeholder and the NaN value is bound to the parameter, like:

INSERT INTO TABLE(ID,DOUBLE_COLUMN) values (?,?)

setParameter 1 => 10

setParameter 2 => Nan

(at least, that seems to have been the case at some time because I have tables with Nan values in them that where inserted in the time our application used parameters for all values), but 

INSERT INTO TABLE(ID,DOUBLE_COLUMN) values (10,NaN)

throws an error. The application currently uses literals for (amongst others) DOUBLE typed values and is now no longer able to copy records from one table to the other if the source value is a Nan.

Is there a way to do this or do we have to revert to using a parameter in this case?

Cheers

Silvio

[Evgenij Ryazanov]

Hello!

NaN is not a literal, it's just an identifier.

In this case you can pass it as a character string literal:

INSERT INTO "TABLE"(ID, DOUBLE_COLUMN) VALUES (10, 'NaN');

In more complex cases where data type cannot be determined automatically a cast is needed:

CAST('NaN' AS DOUBLE PRECISION)

There are two other special values: CAST('Infinity' AS DOUBLE PRECISION) and CAST('-Infinity' AS DOUBLE PRECISION).

REAL and DECFLOAT data types also have these three special values in H2, but all other numeric data types (TINYINT, SMALLINT, INTEGER, BIGINT, and NUMERIC) don't support them.

[Silvio]

Hello Evgenij,

Thanks for the swift response. That is just the information I was looking for. Thank you very much.

Kind regards,

Silvio