After inserting to the table, digits after decimal point changes to 0

Question

While SELECT'ing columns in Vertica, it shows normal numeric values:

SELECT nvl2(exposure_time_ms, ROUND(exposure_time_ms / 1000, CASE WHEN exposure_time_ms < 10000 THEN 1 ELSE 0 END), 0) :: numeric(12,1) AS exposure_seconds

1

But when I am inserting the same thing to the table, which has column 'exposure seconds' type NUMERIC(12,1), it changes all digits after the decimal point to 0:

2

Can you post what value exposure_time_ms contains? Also show us the insert query? — akshay

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-10-24T11:45:23

Presumably, this is because the nvl2() calculation produces an integer. So, try this:

SELECT nvl2(exposure_time_ms,
            ROUND(exposure_time_ms / 1000.0,
                  CASE WHEN exposure_time_ms < 10000 THEN 1 ELSE 0 END
                 ), 0
           )::numeric(12,1) AS exposure_seconds

I will say that it really doesn't make sense to round the values based on the magnitude. You should store the value "as is" and use rounding for presentation -- if it is really needed.

After inserting to the table, digits after decimal point changes to 0

1 Answers