NaN value in STDDEV

Use this forum to post and discuss topics regarding how to do something in Vertica. For instance, instructions on "How to install Vertica"

Moderator: NorbertKrupa

Post Reply
sreediff
Newbie
Newbie
Posts: 15
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: 1671
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 HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

sreediff
Newbie
Newbie
Posts: 15
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: 15
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: 1671
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 HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

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

Who is online

Users browsing this forum: No registered users and 1 guest