How to convert postgres column type from string to float

Question

I have a rails app with a basic postgres db, but I realized that some of my columns are strings and it'd be best if they were floats. I'm trying to convert columns for latitude and longitude from varchar to floating-point.

I've tried this post Rails - gmaps4rails gem on postgres but I kept getting this error, ERROR: invalid input syntax for type double precision: "". I'm willing to try anything else, and I've seen solutions for ways to do it with postgres queries, but I'm uncertain of how to implement them. It's a straightforward problem; "-73.88537758790638" I want to become -73.88537758790638. I just can't seem to find a working solution or one that I understand how to implement.

Answer 1

Empty strings cannot be converted to a number for obvious reasons.
You have to account for that. Replace all occurrences with NULL or 0 or something compatible.

For the number of fractional digits in your example you want the data type numeric, not float - neither real (float4) nor double precision (float8). Those are lossy types and not exact enough. See:

• Fetch records that are non zero after the decimal point in PostgreSQL

Try for yourself:

SELECT '-73.88537758790638'::real             AS _float4
      ,'-73.88537758790638'::double precision AS _float8
      ,'-73.88537758790638'::numeric          AS _numeric;

Result (up to Postgres 11):

_float4  | _float8           | _numeric
---------+-------------------+-------------------
-73.8854 | -73.8853775879064 | -73.88537758790638

db<>fiddle here

Display improved in Postgres 12 (more extra_float_digits by default):

db<>fiddle here

Numeric types in the manual.

Solution

Single SQL statement (replacing empty strings with NULL):

ALTER TABLE tbl
ALTER COLUMN col1 TYPE numeric USING NULLIF(col1, '')::numeric;