6

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.

Community
  • 1
  • 1
tsiege
  • 468
  • 1
  • 4
  • 17

2 Answers2

24

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:

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • How would you do that in psql? – tsiege May 13 '14 at 00:56
  • Awesome, but the only problem is that AR is returning to me the number as a BigDecimal object. Do I have to convert it to a double precision to avoid that? – tsiege May 13 '14 at 01:08
  • @Tsiege: `decimal` and `numeric` are the same in Postgres. `numeric` is the SQL default. You *can* cast to `double precision` but I fail to see why. You'd loose precision. – Erwin Brandstetter May 13 '14 at 01:10
  • I understand, but this is what is returned to me otherwise ```Complaint.first.latitude #=> ``` – tsiege May 13 '14 at 01:15
  • 1
    @Tsiege: How to deal with numeric types in AR and Ruby is another question, really. I am no expert with either. Just answered the question at hand. – Erwin Brandstetter May 13 '14 at 01:25
  • 1
    @Tsiege There's nothing wrong with using a BigDecimal, it is a Numeric just like Fixnum and Float so it shouldn't make any difference in Ruby-land. – mu is too short May 13 '14 at 02:07
  • Gotcha, but I'm going to be doing a lot of mapping, and I don't want any extra method calls. I checked it out on google maps, and it ignores that last decimal point either way. I used the recommended PSQL command above but swapped ```numeric``` with ```double precision```. Thanks, @ErwinBrandstetter for clarifying things. – tsiege May 13 '14 at 02:21
0

This is a really simple thing to do in rails using a native ORM approach:

change_column :restaurants, :column_name, ‘double precision USING CAST(column_name AS double precision)'
boulder_ruby
  • 38,457
  • 9
  • 79
  • 100