Go · Golang · sql

Go SQL error “converting NULL to string is unsupported”


Go is a strongly typed programming language, and many SQL databases also support unknown values. These unknowns can lead to complications in Go. Such as in this example, when a NULL value is in use for an empty string, it throws the unhelpful error converting NULL to string is unsupported.

import (
	"fmt"
)

// LookupName returns the username from database ID.
func LookupName(id int) (string, error) {
	db := Connect()
	defer db.Close()
    var name string
	err := db.QueryRow("SELECT username FROM accounts WHERE id=?", id).Scan(&name)
	if err != nil {
		return "", fmt.Errorf("lookup name by id %q: %w", id, err)
	}
	return name, nil
}
sql: Scan error on column index 0, name "username": 
converting NULL to string is unsupported

Instead of saving SQL query values as basic Go types, database table columns that support NULL or other unknowns should save their values to the database.sql Null types. These include NullString, NullBool, NullInt64, NullFloat64, and NullTime.

import (
	"database/sql"
	"fmt"
)

// LookupName returns the username from database ID.
func LookupName(id int) (string, error) {
	db := Connect()
	defer db.Close()
	var name sql.NullString
	err := db.QueryRow("SELECT username FROM accounts WHERE id=?", id).Scan(&name)
	if err != nil {
		return "", fmt.Errorf("lookup name by id %q: %w", id, err)
	}
	return name.String, nil
}

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s