postgres text field to numeric with empty and null values

Question

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?

Jim Jones · Accepted Answer · 2021-11-04 08:45:58Z

2

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

edited Nov 4, 2021 at 8:45

answered Nov 4, 2021 at 8:32

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2210516 Over a year ago

Yes it gives me the same output but much less code thanks! I still wonder over one thing though. In dbeaver when i hoover over other numeric fields in the table it says numeric(10,2) but if i hoover over this one it says numeric(131089,0)???

Jim Jones Over a year ago

@user2210516 did you see my last edit? SELECT coalesce(nullif(t.config::jsonb ->> 'price',''),'0.00')::numeric(10,2) as price FROM mytable t;

Collectives™ on Stack Overflow

postgres text field to numeric with empty and null values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related