Cast to int instead of decimal?

Question

I have field that has up to 9 comma separated values each of which have a string value and a numeric value separated by colon. After parsing them all some of the values between 0 and 1 are being set to an integer rather than a numeric as cast. The problem is obviously related to data type but I am unsure what is causing it or how to fix it. The problem only exists in the case statement, the split_part function seems to be working perfect.

Things I have tried:

nvl(split_part(one,':',2),0) = COALESCE types text and integer cannot be matched
nvl(split_part(one,':',2)::numeric,0) => Invalid input syntax for type numeric
numerous other cast/convert variations
(CASE WHEN split_part(one,':',2) = '' THEN 0::numeric ELSE split_part(one,':',2)::numeric END)::numeric => runs but get int value of 0

When using the split_part function outside of case statement it does work correctly. However, I need the result to be zero for null values.

split_part(one,':',2) => 0.02068278096187390979 (expected result)

When running the code above I get zero but expect 0.02068278096187390979

Field "one" has the following value 'xyz: 0.02068278096187390979' before the split_part function.

EXAMPLE:

create table test(one varchar); 

insert into test values('XYZ: 0.50000000000000000000') 

select
  one ,split_part(one,':',2) as correct_value_for_those_that_are_not_null ,
  case 
    when split_part(one,':',2) = '' then null 
    else split_part(one,':',2)::numeric 
  end::numeric as this_one_is_the_problem
from test

`create table test(one varchar);` `insert into test values('XYZ: 0.50000000000000000000')` `select one ,split_part(one,':',2) as correct_value_for_those_that_are_not_null ,case when split_part(one,':',2) = '' then null else split_part(one,':',2)::numeric end::numeric as this_one_is_the_problem from test` — datafarmer, May 06 '19 at 21:24
Not in a comment please, it's hard to read there. [Edit] the question instead. — sticky bit, May 06 '19 at 21:25
I ran your code and got the correct numeric value... the only issue I might see is the potential loss of precision. I got "0.5" but not "0" for the "this one is the problem" — Hambone, May 06 '19 at 21:38
Got it, I changed to decimal instead of numeric. Thanks much. — datafarmer, May 06 '19 at 21:48
`decimal` and `numeric` are equivalent in Postgres. Start your questions by disclosing the Postgres version in use, may be relevant. `nvl` is not a Postgres function. — Erwin Brandstetter, May 07 '19 at 01:39

Erwin Brandstetter · Answer 1 · 2019-05-07T01:55:51.803

However, I need the result to be zero for null values.

Your example does not deal with NULL values at all, though. Only addressing the empty string ('').
To replace either with 0 reliably, efficiently and without casting issues:

SELECT part1, CASE WHEN part2 <> '' THEN part2::numeric ELSE numeric '0' END AS part2
FROM (
   SELECT split_part(one, ':', 1) AS part1
        , split_part(one, ':', 2) AS part2
   FROM   test
   ) sub;

See:

Best way to check for "empty or null value"

Also note that all SQL CASE branches must agree on a common data type. There have been minor adjustments in the logic that determines the resulting type in the past, so the version of Postgres may play a role in corner cases. Don't recall the details now.

nvl()is not a Postgres function. You probably meant COALESCE. The manual:

This SQL-standard function provides capabilities similar to NVL and IFNULL, which are used in some other database systems.

Cast to int instead of decimal?

1 Answers1