postgres text field to numeric with empty and null values-CodePudding

Postgres 10.8

I have a JSON field in my Database witch includes a price if there is one.

I get the values like this

select t.config::jsonb ->> 'price' as price from mytable t

It gives me the following results back (null,empty and a price) as a textformat:

[null],'',1330.0

I need to be able to make this field numeric so that i can sum this field later on.

Ive tried to solve it like this:

select 
(
case 
when t.config::jsonb ->> 'price' is null then '0.00'
when t.config::jsonb ->> 'price' = '' then '0.00'
else t.config::jsonb ->> 'price' 
end)::decimal as price 
from mytable t

This gives me a numeric(131089,0) back. I want the field to be like numeric(10,2). There must be a other easier way to do this?

CodePudding user response：

The functions nullif and coalesce can be very handy in this case. nullif(value,'') returns null in case value is empty and coalesce(value,'0.00') returns 0.00 in case value is null, so you might wanna chain both functions like this:

SELECT 
 coalesce(nullif(t.config::jsonb ->> 'price',''),'0.00')::numeric(10,2) as price 
FROM mytable t;

Demo: db<>fiddle