[h2] Re: Error on NaN literal in SQL statement

2023-01-26 Thread Silvio 

Hello Evgenij,

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

Kind regards,

Silvio

On Thursday, 26 January 2023 at 15:25:14 UTC+1 Evgenij Ryazanov wrote:

> 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.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/ffdac278-11ed-4b56-89bb-36036db470b5n%40googlegroups.com.

[h2] Re: Error on NaN literal in SQL statement

2023-01-26 Thread 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.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/97b9ab12-90a9-4b15-90a3-586e55f8ff96n%40googlegroups.com.

[h2] Error on NaN literal in SQL statement

2023-01-26 Thread 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


-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/f98d82cf-f769-4c51-af64-da3714600f67n%40googlegroups.com.