🐞 Resolving sql: Scan error When Handling NULL Strings in Go and PostgreSQL

In Go applications using PostgreSQL, it’s common to map query results into struct fields using the Scan method. However, when database columns contain NULL values, scanning directly into native Go types—like string—can result in runtime errors. One such frequent error occurs when scanning a NULL into a string field:

sql: Scan error on column index 24, name "code": converting NULL to string is unsupported

Root Cause

Go’s database/sql package doesn't allow direct mapping of NULL database values into primitive types. In this case, the discount_code column contains NULL, but the corresponding Go struct expects a plain string, which can't represent NULL. This mismatch triggers the scan error.

The Solution: sql.NullString

Go offers the sql.NullString type—a wrapper that safely handles nullable string fields. Here's how it works:

var code sql.NullString

After scanning:

if code.Valid {    b.code = code.String}

This approach lets you check whether the value is present (Valid == true) before using it.

Refactored Scan Implementation

Below is a corrected version of the scan function:

func (s Storage) scan(scanner db.Scanner) (*Book, error) {
    var b Booking
    var discountCode sql.NullString
    err := scanner.Scan(&b.ID, &discountCode,
    )
    if err != nil {
        return nil, serr.DB("scan", "books", err)
    }
    if discountCode.Valid {
        b.DiscountCode = discountCode.String
    }
    return &b, nil
}

Takeaways

✅ Use sql.Null* types whenever your database may contain NULL values.

✅ Guard assignments to primitive types with .Valid checks to avoid panics.

✅ Encapsulate conversions to clean up your logic — consider writing helpers for mapping sql.NullString to string.