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 ?


  • Options
    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;

  • Options
    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