How to convert postgres column type from string to float
activerecord, database-normalization, floating-point-conversion, postgresql, ruby-on-rails
Solution
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;
Problem
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.