NaN value in STDDEV

Moderator: NorbertKrupa

Post Reply
sreediff
Newbie
Newbie
Posts: 18
Joined: Mon Mar 28, 2016 2:51 pm

NaN value in STDDEV

Post by sreediff » Wed May 25, 2016 5:14 pm

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 ?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: NaN value in STDDEV

Post by JimKnicely » Wed May 25, 2016 5:28 pm

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?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

sreediff
Newbie
Newbie
Posts: 18
Joined: Mon Mar 28, 2016 2:51 pm

Re: NaN value in STDDEV

Post by sreediff » Wed May 25, 2016 5:33 pm

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

sreediff
Newbie
Newbie
Posts: 18
Joined: Mon Mar 28, 2016 2:51 pm

Re: NaN value in STDDEV

Post by sreediff » Wed May 25, 2016 5:46 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: NaN value in STDDEV

Post by JimKnicely » Thu May 26, 2016 5:45 pm

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica "How to..."”