Testing for NaN with FLOAT

mark_d_drake

 select cast(double_precision_col as varchar(39)) , case when double_precision_col = 'NAN' then NULL else cast(double_precision_col as numeric(616,308)) end from t_postgres.numeric_types;

returns

ERROR 3425:  Float "NaN" is out of range for type numeric(616,308)

But according to https://www.vertica.com/blog/vertica-quick-tip-query-nan-values/ I don't see why ?

mosheg

The right way to check if something is NaN is to ask if it is not equal to itself, like mentioned in the tip.
For example:
select case when (616308)::numeric != (616308)::numeric then NULL else (616308)::numeric end from dual;

mark_d_drake

Will try it. Thx

B4 I go to far down the road to hell, is there any easy way to render a double to 18 digits precision as a string in vertica. The stupid cast above to numeric(616,308) is a step in that direction. I was basically going to cast my double to the numeric, covert the numeric to a string, extract the first 18 non-zero digits, adjust/re-insert the decimal point append the exponent. - I think that gets me there, but surely there is an easier way.

Before anyone points out that the double is only acurate to 15 digits, I am well aware of that, however if you are looking to transport the exact value that was inserted somewhere else, then all 18 digits are required.