PostgreSQL Can't Convert String to double precision

Question

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!

One or more records have a value in the field that can't be converted to a double precision number. See https://stackoverflow.com/questions/16195986/isnumeric-with-postgresql for a suggested function to check if a value is numeric. — Brian, Jul 15 '20 at 18:37

Adrian Klaver · Accepted Answer · 2020-07-15T19:05:15.220

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.

In the question the error has `«1,100.00»` which leads me to think it might be a locale difference where thousands and decimal separators are different. For example one thousand dollars and ten cents is `1 000,10` for French locales and `1,000.10` for English and `1.000,10` for Italian. — Brian, Jul 15 '20 at 18:51
Ok got it! I'm new using PostgreSQL so it's taking me a while to spot the little differences between MySQL and this one. Thanks a lot! — Roux Casas, Jul 15 '20 at 19:16

score -1 · Answer 2 · answered Apr 18 '22 at 18:28

-1

hi you can tried with in case this number-text contein comma use ::

REPLACE(ap_price, ',', '.' )::double precision

answered Apr 18 '22 at 18:28

Gus Ch

9
2

PostgreSQL Can't Convert String to double precision

2 Answers2