NaN value in STDDEV

[sreediff]

I am getting STDDEV and trying to insert output to a table.
However output is coming as NaN for one row causing bulk load to fail. Is NaN defined so that we can limit the value ?

[JimKnicely]

Hi,

NaN (Not a Number) is defined. I believe it can only be inserted into a column having a data type of either DOUBLE PRECISION, FLOAT, FLOAT(n), FLOAT8 or REAL.

Example:

Code: Select all

dbadmin=> create table test (c float);
CREATE TABLE

dbadmin=> copy test from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1.2
>> NaN
>> \.

dbadmin=> select * from test;
  c
-----
 1.2
 NaN
(2 rows)

What error are you getting?

[sreediff]

- ERROR: Float "nan" is out of range for type numeric(16,5) . What should be datatype/length to be used for inserting NaN ?

[sreediff]

if i give float insert NaN:float will insert data but can we give limit to float datatype ?

[JimKnicely]

Hi,

FLOAT is the only data type supporting NaN.

Although you can use the syntax FLOAT(n) where n must be between 1 and 53, inclusive, a 53-bit fraction is always used. So no, you can't limit a float.

But there are always work a-rounds... Example:

Code: Select all

dbadmin=> create table test (c float);
CREATE TABLE

dbadmin=> insert into test values (1.11111);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('NaN');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values (3.21325452);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test;
     c
------------
    1.11111
        NaN
 3.21325452
 
 dbadmin=> select case when c::varchar = 'NaN' then c else c::varchar::numeric(3,2)::float end from test;
  c
------
 1.11
  NaN
 3.21
(3 rows)

Using the CASE statement I was able to handle NaN values and limit the size of the other values.