Testing for NaN with FLOAT

mark_d_drakemark_d_drake Community Edition User
 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;


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 ?


  • moshegmosheg Vertica Employee Administrator

    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_drakemark_d_drake Community Edition User

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file