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.

Erwin Brandstetter · Accepted Answer · 2021-03-12 02:27:56Z

26

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;

edited Mar 12, 2021 at 2:27

answered May 13, 2014 at 0:43

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

tsiege Over a year ago

How would you do that in psql?

tsiege Over a year ago

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?

Erwin Brandstetter Over a year ago

@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 Over a year ago

@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.

mu is too short Over a year ago

@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.

|

boulder_ruby · Accepted Answer · 2016-07-07 23:00:10Z

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)'

answered Jul 7, 2016 at 23:00

boulder_ruby

40k9 gold badges82 silver badges104 bronze badges

1 Comment

Wedge Martin Over a year ago

Bit of a postgres n00b here, but when I try this, PG doesn't like it: syntax error near 'USING'

Collectives™ on Stack Overflow

How to convert postgres column type from string to float

2 Answers 2

Solution

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solution

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related