Postgres cast split string to double precision

Question

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 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. Thanks

Answer 1

To provide a general answer to this question, I did the following (all the code below is available on the fiddle here).

• Just a note before we start. This is a signal lesson about UI design. NEVER, EVER let users enter text (if at all possible)! For example, the currency, if there's a choice, have a dropdown - don't let users enter a three-letter code! Likewise with the amount - validate it:

1. validate it on data-entry at the first point of sale/contact - e.g. amount >= 0, correct number of decimal points, commas &c., no trailing or leading spaces...

and

2. validate on INSERTion into to the database (amount >= 0, formatting is not for the database - just store a number in a field of the appropriate type for your currency - leave any formatting to your app/Excel... enforce any other minimum and/or maximum constraints also. Put as much control into the table's DDL as possible. You could use ON INSERT triggers for even more control!

• The Golden Rule (of UI design) is that if you allow users the possibility of messing up, then they will find a way of doing so!

• Final word on this matter - store relevant datums together - the currency field in its own 3 letter code field - the actual amount in a DOUBLE PRECISION or REAL or whatever. Avoid VARCHAR()/TEXT fields with a mix of data.

Anyway now, down to brass tacks!

I created a sample table:

CREATE TABLE payment
(
  amount TEXT NOT NULL
);

and populated it with "data":

INSERT INTO payment VALUES
('KES 0.80'),             
('KES  0.80'),            
('KES .80'),           
('xyzKES 0.80'),          
('KES . 0.80'),           
('KES 0.80afasf'),          
('KES 0.80__asdfa'),
('KES 0.80..asdfasdf'),
('KES 0.8.0..asdfasdf'),
('KES 0.8.0..asdfasdf');

Then, the tidying up step uses the REGEXP_REPLACE() function. The key thing about this function is to remember that the 'g' flag is important - with it, everything that matches is replaced, without, only the first occurrence of the pattern is replaced!

SELECT
  REGEXP_REPLACE(amount, '[^0-9\.]+|\. +|\.0|\.{2,}', '', 'g')::DOUBLE PRECISION
FROM payment;

Result:

regexp_replace
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8
0.8

It's not perfect, but should do the job adequately. If you're interested, you can check out the wreckage of my previous attempts here. I would urge you to check out regular expressions here - they are tricky as you will see if you check out my previous efforts. There are many other regular expression sites.

Answer 2

It stores the costs of each service a client gets in this format "KES 0.80".

This looks to be two data items stored in a single field.
That breaks very basic, Data Normalisation rules.

If this represents Currency and Value then you should store these in two separate fields (of the correct Data Types) and put the two back together again when you retrieve the data.

Doing so would completely remove the need for your current, casting exercise.

Answer 3

Not all your rows contain exactly that format, some may have two spaces between "KES" and the value. eg.:

ghp=> select sum(cast(split_part('KES  0.80', ' ', 2) as double precision));
ERROR:  invalid input syntax for type double precision: ""

Answer 4

The problem is that an empty string '' isn't a valid number and thus the 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.