Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Go database/sql fails to scan a NULL column, and stores empty strings instead of NULL

Errorconverting NULL to string is unsupported

Short answer

database/sql hands a NULL to Scan as nil, and a plain *string cannot hold it, so Scan returns an error. Scan into sql.NullString (or sql.Null[string] on Go 1.22+) and check Valid. For writes, pass a NullString with Valid set to false, or a plain nil, so the database receives NULL instead of an empty string.

  • Applies to Go 1.24, database/sql, MySQL 8.4, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find which column is nullable. A column that allows NULL is the only one that can trigger this, and the error names its index and name.

    DESCRIBE customer;   -- MySQL
    -- or in psql: \d customer
  2. 2Match what you see, then jump to its fix

    1. 1A Scan error about converting NULL, or the older text storing driver.Value type <nil>A nullable column is scanned into a plain stringFix
    2. 2The column shows '' in the database, and IS NULL finds no rowsAn empty string is written where NULL was intendedFix

What does the error look like?

  • converting NULL to string is unsupported
  • unsupported Scan, storing driver.Value type <nil> into type *string

Cause 1 of 2

A nullable column is scanned into a plain string

1Check

The Scanner interface documentation lists nil as the value a driver passes for NULL. A *string destination has no way to represent it.

grep -n "Scan(&" *.go

2Fix

Scan into sql.NullString and read String only when Valid is true. From Go 1.22 the generic sql.Null[string] does the same.

var s sql.NullString
err := db.QueryRow("SELECT name FROM customer WHERE id = ?", id).Scan(&s)
if err != nil {
    return err
}
name := "Valued Customer"
if s.Valid {
    name = s.String
}

// Go 1.22+
var g sql.Null[string]
// g.V holds the value, g.Valid says whether it was NULL

3Verify

Run the query on a row that holds NULL and a row that holds text; both scan without error.

go test ./...

Confirmed by go.dev

Did this fix it?

Cause 2 of 2

An empty string is written where NULL was intended

1Check

A Go string has no NULL state, so an empty string is sent as an empty string. NullString implements driver.Valuer: with Valid false it is sent as NULL.

SELECT COUNT(*) FROM customer WHERE phone IS NULL;

2Fix

Build a NullString from the input, valid only when the string is not empty, and pass that as the argument.

phone := sql.NullString{String: input, Valid: input != ""}
_, err := db.Exec("INSERT INTO customer (name, phone) VALUES (?, ?)", name, phone)
// PostgreSQL with lib/pq or pgx uses $1, $2 instead of ?

3Verify

Insert a row with an empty input and check that the column is NULL.

SELECT id, phone IS NULL FROM customer ORDER BY id DESC LIMIT 1;

Confirmed by pkg.go.dev

Did this fix it?

How do I stop it coming back?

  • Make columns NOT NULL with a default when NULL has no meaning, so Go code can use plain strings.
  • Use the Null types in the struct fields that map to nullable columns.
  • Remember that ? is for MySQL; Postgres drivers use $1, $2.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.