使用Go和数据库/ SQL扫描nil float值时出错

Question

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 "<nil>" to a float64: strconv.ParseFloat:
parsing "<nil>": 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 (
    "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
", 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
", err)
        return
    }

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

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

The structure of the table is as follows :

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

Answer 1

Many thanks, that worked :

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

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