CODEWITHSUNDEEP
sqljsonpostgresqlcasting
user2210516
user2210516

Reputation: 683

postgres text field to numeric with empty and null values

Postgres 10.8

I have a JSON field in my Database which 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.

I've 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?

Upvotes: 1

Views: 1381

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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

Upvotes: 2

Related Questions