Scan error: unsupported Scan, storing driver.Value type <nil> into type *string

Question

When I try to fetch Articles without photo from mysql database:

func ArticlesAllText() ([]Article, error) {
    var err error
    var result []Article
    err = database.SQL.Select(&result, "SELECT * FROM Article WHERE photo IS NULL")
    if err != nil {
        log.Println(err)
    }
    return result, standardizeError(err)
}

I get

sql: Scan error on column index 10: unsupported Scan, storing driver.Value type into type *string

When the field value is NULL.

How can I fix this?

Answer 1

Use sql.NullString.

https://godoc.org/database/sql#NullString

Personally, I hate this solution, which is why I normalize my DB so that there are no NULLs.

Either change

var result []Article

To

var result []sql.NullString

Then take those results and make a []Article using the checks from the documentation.

Or in your struct, change

Photo *string

To

Photo sql.NullString

And deal with the annoyance of it being a struct instead of a *string

Thanks to ATN, see here for a guide https://medium.com/aubergine-solutions/how-i-handled-null-possible-values-from-database-rows-in-golang-521fb0ee267

Answer 2

You can use any of the below two solutions:-

1. You can use sql.NullString to handle the field before using scan(). OR
2. You can replace all the possible NULL values with the desired string say '' from the query itself.

For implementing the 1st solution refer to the @RayfenWindspear answer. For the 2nd solution update the query as below:-

SELECT colm1, colm2, COALESCE(photo, '') photo, colm4 FROM Article WHERE photo IS NULL

For MySQL use IFNULL() or COALESCE() function to return an alternative value if an expression is NULL:

For SQL Server use IFNULL() or COALESCE() function for the same result

MS Access use IsNull function for the same result

For Oracle use NVL() function for the same result

Reference: https://www.w3schools.com/sql/sql_isnull.asp