使用Go和database/sql进行扫描时出现了空的浮点值扫描错误。

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

Error with scan of nil float value using Go and database/sql

问题

我正在编写一个程序,需要在执行一些插入和更新操作之前确定表的初始值。所涉及的表(在这种情况下是PostgreSql)最初可能没有任何行。当我选择初始值时,如果没有任何行,余额值的总和将返回为nil。这会导致扫描失败,并显示以下错误消息:

在扫描test01的开头行数时出错。错误=sql: 在第1列上扫描错误: 将字符串“<nil>”转换为float64: strconv.ParseFloat: 解析“<nil>”时出现无效语法

虽然我可以通过进行两次查询来“解决”这个问题,一次查询COUNT(*),另一次查询balances的SUM(),如果行数超过零,则可以解决问题,但这似乎不是一个优雅的解决方案,并且可能并不总是解决问题,而且所选择的两个值(行数和余额总和)不是在同一时间点上选择的。

有没有办法通过一次查询解决这个问题?

下面是一个说明问题的小测试程序。当选择的表中有行时,程序正常工作。但是,如果没有任何行,就会出现上述错误。

示例测试程序:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

var db *sql.DB

func main() {
    var err error

    db, err = sql.Open("postgres",
              "user=test dbname=testdb password=test sslmode=disable")
    if err != nil {
        fmt.Sprintf("Failed to open Db Connection. Error = %s\n", err)
        return
    }

    defer fCloseDb()

    var row *sql.Row
    var sSql string = "SELECT COUNT(*), SUM(dbalance) FROM test01"
    if row = db.QueryRow(sSql); row == nil {
        println("No row returned selecting opening count(*) from test01")
        return
    }

    var iRowCount int64 = 0
    var fBalTot float64 = 0.00

    if err = row.Scan(&iRowCount, &fBalTot); err != nil {
        fmt.Printf("Error on scan of test01 opening Row Count. Error = %s\n", err)
        return
    }

    fmt.Printf("Row Count = %d, Balance total value = %.2f\n", iRowCount, fBalTot)
}

func fCloseDb() {
    if db != nil {
        db.Close()
        println("Db Closed")
    }
}

表的结构如下:

sSql = "CREATE TABLE IF NOT EXISTS test01 " +
       "(ikey SERIAL Primary Key, " +
       "sname varchar(22) not null, " +
       "dbalance decimal(12,2) not null)"
英文:

I'm writing a program that needs to determine opening values for a table prior to doing some Inserts and Updates for that table. The table in question (PostgreSql in this case) could have zero rows initially. When I select the opening values, if there are zero rows, the total for the value of balances is being returned as nil. This causes the scan to fail with message :

Error on scan of test01 opening Row Count. Error = sql: Scan error on column
index 1: converting string &quot;&lt;nil&gt;&quot; to a float64: strconv.ParseFloat:
parsing &quot;&lt;nil&gt;&quot;: invalid syntax

While I can "solve" the problem by doing two selects, one to select the COUNT(*) and the other to SUM() the balances if the row-count exceeds zero, it does not seem an elegant solution, and may not always solve the problem, and the two values selected (number of rows and total of balances) are not at the same point in time.

Is there a way to solve this problem doing one select of the table?

A small test program illustrating the problem is below. When there are rows in the table being selected, the program works fine. However if there are zero rows, the above error results.

Example Test Program:

package main

import (
    &quot;database/sql&quot;
    &quot;fmt&quot;
    _ &quot;github.com/lib/pq&quot;
)

var db *sql.DB

func main() {
    var err error

    db, err = sql.Open(&quot;postgres&quot;,
              &quot;user=test dbname=testdb password=test sslmode=disable&quot;)
    if err != nil {
        fmt.Sprintf(&quot;Failed to open Db Connection. Error = %s\n&quot;, err)
        return
    }

    defer fCloseDb()

    var row *sql.Row
    var sSql string = &quot;SELECT COUNT(*), SUM(dbalance) FROM test01&quot;
    if row = db.QueryRow(sSql); row == nil {
        println(&quot;No row returned selecting opening count(*) from test01&quot;)
        return
    }

    var iRowCount int64 = 0
    var fBalTot float64 = 0.00

    if err = row.Scan(&amp;iRowCount, &amp;fBalTot); err != nil {
        fmt.Printf(&quot;Error on scan of test01 opening Row Count. Error = %s\n&quot;, err)
        return
    }

    fmt.Printf(&quot;Row Count = %d, Balance total value = %.2f\n&quot;, iRowCount, fBalTot)
}

func fCloseDb() {
    if db != nil {
        db.Close()
        println(&quot;Db Closed&quot;)
    }
}

The structure of the table is as follows :

sSql = &quot;CREATE TABLE IF NOT EXISTS test01 &quot; +
       &quot;(ikey SERIAL Primary Key, &quot; +
       &quot;sname varchar(22) not null, &quot; +
       &quot;dbalance decimal(12,2) not null)&quot;

答案1

得分: 1

非常感谢,那个方法有效:

"SELECT COUNT(*), coalesce(SUM(dbalance), 0.00) FROM test01"

我相信coalesce函数返回第一个非空值。

英文:

Many thanks, that worked :

&quot;SELECT COUNT(*), coalesce(SUM(dbalance), 0.00) FROM test01&quot;

I believe that coalesce returns the first non-null value.

huangapple
  • 本文由 发表于 2013年9月24日 18:06:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/18978611.html
匿名

发表评论

匿名网友

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

确定