PostgreSQL Can’t Convert String to double precision

So I’m asked to do this query for a college project:

SELECT l.city AS name, AVG((pr.ap_price::double precision
                            * 7 - pr.weekly::double precision)
                           /((pr.ap_price::double precision))*100) AS savings_percentage 
FROM locations AS l, price AS pr, apartments AS ap, hosts AS h
WHERE pr.id_apt = ap.id_apartment AND l.id_apt = ap.id_apartment AND h.host_id = ap.id_host AND h.host_identity_verified = 't'
GROUP BY l.city
ORDER BY savings_percentage DESC LIMIT 3;

Where pr.ap_price and pr.ap_weekly are both saved as strings (VARCHAR(255)), and I’ve tried to cast them as other types wether using CAST(pr.ap_price AS double precision) or using the option found in the code above. It always ends up showing the same error wether I cast it to numeric or double precision: ERROR: la sintaxis de entrada no es válida para tipo double precision: «1,100.00» SQL state: 22P02 (In Spanish but basically says ERROR: the entrance syntaxis isn’t valid for type double precision)

How can I cast it correctly? My objective is to turn it from string to numbers to be able to compare them.

I have also tried using: ALTER TABLE apartments_importacio ALTER COLUMN price TYPE double precision USING (price::double precision); but the same error shows. (Where apartments_importacio is the table where I copy the .csv file to later insert into the other tables)

Any clues? Thanks in advance!

Answer

I’m going to say it is because of this:

select ''::double precision;
ERROR:  invalid input syntax for type double precision: ""
LINE 1: select ''::double precision;

select '10'::double precision;
 float8 
--------
     10
(1 row)

The reason being an empty string is not a valid number.

One solution is to do:

select nullif(trim('  '), '')::double precision;
 nullif 
--------
   NULL
(1 row)

--- So for your case:

nullif(trim(pr.ap_price), '')::double precision

Otherwise you will need to go through an clean up the empty strings in those columns and change them to 0 or NULL. Long term I would say making those field float8 or better yet numeric is the better solution. Then you deal with the issue on creating the record.

UPDATE. Dealing with number formatting:

select '1,100.00'::double precision;
ERROR:  invalid input syntax for type double precision: "1,100.00"
LINE 1: select '1,100.00'::double precision;
               ^
select '1100.00'::double precision;
 float8 
--------
   1100
(1 row)

The solution to above is:

select to_number('1,100.00', '9999.99')::double precision;
 to_number 
-----------
      1100
(1 row)

For more information see

https://www.postgresql.org/docs/current/functions-formatting.html

to_number(text, text)   numeric     convert string to numeric   to_number('12,454.8-', '99G999D9S')```

See:

Table 9.27 shows the template patterns available for formatting numeric values.

For more information on your options for formatting.