为什么Go将PostgreSQL的numeric和decimal列视为[]uint8类型?

huangapple go评论85阅读模式
英文:

Why does Go treat a Postgresql numeric & decimal columns as []uint8?

问题

我不确定这是Go中的一个错误还是我不理解的问题。我有以下代码:

package main

import (
  "database/sql"
  "log"
  "reflect"

  _ "github.com/lib/pq"
)

func main() {
  Db, err := sql.Open("postgres", "user=yoitsme password=openupitsme host=x.x.x.x dbname=mydb")
  if err != nil {
    log.Println(err)
  }
  rows, err := Db.Query("SELECT 1.3250::numeric, 8.548::decimal, 908.234::float, 1234::integer")
  defer rows.Close()
  for rows.Next() {
    var col1, col2, col3, col4 interface{}
    if err := rows.Scan(&col1, &col2, &col3, &col4); err != nil {
      log.Println(err)
    }
    log.Println(col1, reflect.TypeOf(col1))
    log.Println(col2, reflect.TypeOf(col2))
    log.Println(col3, reflect.TypeOf(col3))
    log.Println(col4, reflect.TypeOf(col4))
  }
  if err = rows.Err(); err != nil {
    log.Println(err)
  }

}

这会打印出:

2015/08/11 09:35:47 [49 46 51 50 53 48] []uint8
2015/08/11 09:35:47 [56 46 53 52 56] []uint8
2015/08/11 09:35:47 908.234 float64
2015/08/11 09:35:47 1234 int64

所以,我得到了[]uint8(例如字符串)作为实际上是数字的前两列。最后两列与预期相符。根据Postgresql,Numeric和Decimal类型是SQL标准的一部分。那么,为什么Go在他们的database/sql包中没有遵循SQL标准呢?是因为Go没有内置的"Decimal"类型吗?由于语言的缺陷,数据库/sql包将数字转换为字符串似乎是错误的...

英文:

I'm not sure if this is a bug in Go or just something I don't understand. I have the following:

package main

import (
  "database/sql"
  "log"
  "reflect"

  _ "github.com/lib/pq"
)

func main() {
  Db, err := sql.Open("postgres", "user=yoitsme password=openupitsme host=x.x.x.x dbname=mydb")
  if err != nil {
    log.Println(err)
  }
  rows, err := Db.Query("SELECT 1.3250::numeric, 8.548::decimal, 908.234::float, 1234::integer")
  defer rows.Close()
  for rows.Next() {
    var col1, col2, col3, col4 interface{}
    if err := rows.Scan(&col1, &col2, &col3, &col4); err != nil {
      log.Println(err)
    }
    log.Println(col1, reflect.TypeOf(col1))
    log.Println(col2, reflect.TypeOf(col2))
    log.Println(col3, reflect.TypeOf(col3))
    log.Println(col4, reflect.TypeOf(col4))
  }
  if err = rows.Err(); err != nil {
    log.Println(err)
  }

}

This prints:

2015/08/11 09:35:47 [49 46 51 50 53 48] []uint8
2015/08/11 09:35:47 [56 46 53 52 56] []uint8
2015/08/11 09:35:47 908.234 float64
2015/08/11 09:35:47 1234 int64

So, I get []uint8 (e.g. a string) for the first two columns that are actually numbers. The last 2 columns are as expected. According to Postgresql Numeric & Decimal types are part of the SQL standard. So, why doesn't Go follow the SQL standard in their database/sql package? Is it because Go doesn't have a builtin "Decimal" type? It seems wrong that the database/sql package turned a number into a string because of a shortcoming in the language....

答案1

得分: 15

因为没有更好的解决方案。(至少在Go 1.5的big.Float之前是这样的)。还有其他的选择吗?

  • 转换为整数。显然这是一个糟糕的解决方案,因为数字可以有小数部分。

  • 转换为float64。这是不好的。特别是当你在处理货币时(其中numericdecimal类型最常用)。

这个特定的数据库驱动程序选择返回一个包含数字的字符串 - 让决定,是丢失一些精度(通过将其转换为带有strconvfloat64)还是使用一个十进制/精确数字库(如gmpmath/big)。

英文:

Because there is no better solution. (At least wasn't until Go 1.5's big.Float). What other alternatives are there?

  • Turn it into an integer. Obviously a bad solution since numerics can have a fractional part.

  • Turn it into a float64. This is evil. Especially if you're working with money (where types like numeric and decimal have the most usage).

This particular database driver chooses instead to return a string containing the number - to let you decide, whether to lose some precision (by converting it into a float64 with strconv) or use a decimal/precise number library (like gmp or math/big).

答案2

得分: 2

这是开发者对那个问题的回答的一个问题:https://github.com/lib/pq/issues/648

> 使用float64类型来表示十进制数是不安全的,因为浮点数无法表示所有的十进制数。例如,十进制数支持比浮点数大得多的指数,并且当将浮点数的系数转换为其二进制表示时,会发生变化。在Go语言中,将字符串转换为浮点数是很简单的,所以我们将保持这种行为。

英文:

Here is an issue with the answer to that questions from the developers: https://github.com/lib/pq/issues/648

> It's not safe to use a float64 type for a decimal because floats can't represent all decimals. For example, decimals support exponents much larger than floats, and the coefficients of floats, when converted to their base-2 representation, will change. It is trivial in go to convert a string to a float, so we are going to keep this behavior.

huangapple
  • 本文由 发表于 2015年8月11日 23:46:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/31946344.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定