postgresql 如何将 postgres 列类型从字符串转换为浮点数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23621077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert postgres column type from string to float
提问by tsiege
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.
我有一个带有基本 postgres db 的 rails 应用程序，但我意识到我的一些列是字符串，最好是它们是浮点数。我正在尝试将纬度和经度列从 varchar 转换为浮点数。
I've tried this post Rails - gmaps4rails gem on postgresbut 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.
我已经在 postgres 上尝试过这篇文章Rails - gmaps4rails gem，但我一直收到这个错误，ERROR: invalid input syntax for type double precision: "". 我愿意尝试其他任何方法，并且我已经看到了使用 postgres 查询的方法的解决方案，但我不确定如何实现它们。这是一个简单的问题；“-73.88537758790638”我想变成-73.88537758790638。我似乎无法找到可行的解决方案或我了解如何实施的解决方案。
回答by Erwin Brandstetter
Empty stringscannot be converted to a number for obvious reasons.
You have to account for that. Replace all occurrences with NULLor 0or something compatible.
由于显而易见的原因，空字符串不能转换为数字。
你必须考虑到这一点。用NULL或0或兼容的东西替换所有出现的事件。
Also 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 by far. Just try and see:
同样对于示例中的小数位数，您需要数据类型numeric，而不是浮点数 - 既不是real( float4) 也不是double precision( float8)。这些是有损类型，目前还不够准确。试试看：
SELECT '-73.88537758790638'::real AS _float4
,'-73.88537758790638'::double precision AS _float8
,'-73.88537758790638'::numeric AS _numeric;
Result:
结果：
_float4 | _float8 | _numeric
---------+-------------------+-------------------
-73.8854 | -73.8853775879064 | -73.88537758790638
Solution
解决方案
Single SQL statement (replacing empty strings with NULL):
单个 SQL 语句（用 替换空字符串NULL）：
ALTER TABLE tbl
ALTER COLUMN col1 TYPE numeric USING NULLIF(col1, '')::numeric;
回答by boulder_ruby
This is a really simple thing to do in rails using a native ORM approach:
这是使用原生 ORM 方法在 Rails 中做的一件非常简单的事情：
change_column :restaurants, :column_name, ‘double precision USING CAST(column_name AS double precision)'
