Golang : Scan error on column index 1, name "MIDDLE_NAME": converting NULL to string is unsupported

Question

I have below golang code which is working fine when there is no null value in the table. But in my table some column called "MIDDLE_NAME" has null value and hence I am getting error

Scan error on column index 1, name "MIDDLE_NAME": converting NULL to string is unsupported

How do I handle null value in err := rows.Scan(columnPointers...)

package main


import (
    "database/sql"
    "log"
    "strings"
)

func printColumns(rows *sql.Rows) {
    col, err := rows.Columns()
    onError(err)
    var sb strings.Builder
    sb.WriteString("|")
    for _, s := range col {
        sb.WriteString(s)
        sb.WriteString("|")
    }
    log.Println(sb.String())
}

func printRows(rows *sql.Rows) {
    cols, _ := rows.Columns()
    for rows.Next() {
        columns := make([]string, len(cols))
        columnPointers := make([]interface{}, len(cols))
        for i := range columns {
            columnPointers[i] = &columns[i]
        }

        err := rows.Scan(columnPointers...)
        if err != nil {
            panic(err)
        }
        var sb strings.Builder
        sb.WriteString("|")
        for _, s := range columnPointers {
            sb.WriteString(*s.(*string))
            sb.WriteString("|")
        }
        log.Println(sb.String())
    }
}

func onError(err error) {
    if err != nil {
        log.Printf("Error %s", err)
        panic(err)
    }
}

Pontefract

You need pointer types or custom sql.Scanner types to be able to scan columns with null values. – Concomitance

@Concomitance Can you please elaborate? i am pretty new to Golang and pointers? – Pontefract

In Go string values cannot be nil so scanning db NULLs into strings is not allowed, hence the error. Pointer values can be nil however, therefore you should use, in this specific case, a pointer to a string rather than just string. The alternative is to use Go types that "know" how to handle db NULLs, i.e. sql.Scanner implementations, e.g. sql.NullString. Or, another alternative, is to use COALESCE in the SQL query. – Concomitance

@Concomitance This is exactly what I was looking for as an answer. You should add it outside of a comment to help the next person get to it more easily. – Splanchnology

Answer 1

You can using IFNULL(field, default_value) to return default value if field is null

Relieve