如何将PostgreSQL列类型从字符串转换为浮点数

Question

我有一个基本的Postgres数据库，其中一些列是字符串类型，但我意识到最好将它们转换为浮点型。我试图将经纬度列从varchar转换为浮点型。
我尝试了这篇文章Rails - gmaps4rails gem on postgres但我不断遇到这个错误：ERROR: invalid input syntax for type double precision: ""。我愿意尝试任何其他方法，并且我已经看到了使用Postgres查询的解决方案，但我不确定如何实施。这是一个简单的问题;“-73.88537758790638”我想变成-73.88537758790638。我似乎找不到一个可行的解决方案或者我无法理解如何实现它。

- tsiege

Answer 1

显然，空字符串不能转换为数字，你需要考虑这一点。将所有出现的空字符串替换为NULL或0或兼容的内容。

对于你的示例中小数位数的数量，你需要使用数据类型numeric，而不是real（float4）或double precision（float8）。这些是有损类型，不够精确。请参见：

• 在PostgreSQL中获取小数点后非零的记录

可以自己尝试：

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

结果（适用于Postgres 11及以下版本）：

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

db<>fiddle 这里

Postgres 12中显示得到改进（默认情况下有更多的extra_float_digits）：

db<>fiddle 这里

手册中的数字类型。

解决方案

使用单个SQL语句（将空字符串替换为NULL）：

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

- Erwin Brandstetter

---

你会如何在psql中实现这个？ - tsiege

太棒了，但唯一的问题是AR将数字作为BigDecimal对象返回给我。我需要将其转换为双精度以避免这种情况吗？ - tsiege

我明白，但是这是返回给我的内容：Complaint.first.latitude #=> <BigDecimal:7fd9606acb88,'0.4067606251 08629E2',27(27)> - tsiege

@Tsiege：如何处理AR和Ruby中的数字类型是另一个问题。我对这两个都不是专家。只是回答了手头的问题。 - Erwin Brandstetter

@Tsiege，在使用BigDecimal时没有任何问题，它与Fixnum和Float一样是数字类型，在Ruby中不应该有任何区别。 - mu is too short

Answer 2

使用本地ORM方法在Rails中完成这个任务非常简单：

change_column :restaurants, :column_name, ‘double precision USING CAST(column_name AS double precision)'

- boulder_ruby

---

作为一个 PostgreSQL 的新手，当我尝试这个操作时，PG 报错了：'USING' 附近有语法错误。 - Wedge Martin