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?
>Solution :
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