Which method should I choose to postgres cast split string to double precision?

433 Asked by BuffyHeaton in SQL Server , Asked on Feb 7, 2023

I have a cost column in my postgres db whose type is varchar. It stores the costs of each service a client gets in this format "KES 0.80". I am trying to sum up everything to get a cost breakdown.

This is how my query looks

select sum(cast(split_part("cost", ' ', 2) as double precision)) from send_log;

I am having a challenge casting the string to double precision so that I can get the sum. This is the error i am getting

ERROR: invalid input syntax for type double precision: ""

And if i try to cast into an integer i get this error

ERROR: invalid input syntax for integer: "0.8000"

I am looking for a better method to cast the string to double precision or advise on how I can format my query to get the total sum from the cost column. Any pointers or useful links will be appreciated.

Answered by Aalap prabhakaran

The problem is that an empty string '' isn't a valid number and thus the postgres cast fails. One way to deal with this is to use nullif() to turn an empty string into a null value:

cast(nullif(split_part("cost", ' ', 2), '') as double precision)

Maybe even combine that with trimming leading and trailing spaces:

cast(nullif(trim(split_part("cost", ' ', 2)), '') as double precision)

But the correct solution to that problem is to split up the column into two columns as suggested by Phil W.

Which method should I choose to postgres cast split string to double precision?

Your Answer