Scan NULL Values in Go With Null Types and Pointers

SQL has a value Go does not: NULL, meaning "unknown or absent". A column can hold it unless it is declared NOT NULL, and database/sql has to put it somewhere when it scans a row into Go variables. A Go string cannot be absent, so scanning NULL into one fails. Every Go program that reads from a real schema meets this within the first week. This article shows the options on Go 1.25.6, with the actual errors and the JSON each option produces.

The failure

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"

	_ "modernc.org/sqlite"
)

type User struct {
	ID    int
	Name  string
	Email *string        // nil when NULL
	Nick  sql.NullString // Valid=false when NULL
	Age   sql.Null[int]  // generic form, Go 1.22
}

func main() {
	db, err := sql.Open("sqlite", "file:lab583?mode=memory&cache=shared")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	_, _ = db.Exec(`create table u (id int, name text not null, email text, nick text, age int);
		insert into u values (1, 'ada', '[email protected]', 'countess', 36), (2, 'grace', null, null, null)`)

	// 1. Scanning NULL into a plain string fails.
	var s string
	err = db.QueryRow(`select email from u where id = 2`).Scan(&s)
	fmt.Printf("plain string: %v\n", err)

	// 2. Three ways that work.
	rows, _ := db.Query(`select id, name, email, nick, age from u order by id`)
	defer rows.Close()
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.Nick, &u.Age); err != nil {
			log.Fatal(err)
		}
		email := "<nil>"
		if u.Email != nil {
			email = *u.Email
		}
		fmt.Printf("id=%d email=%s nick.Valid=%v nick=%q age.Valid=%v age=%d\n", u.ID, email, u.Nick.Valid, u.Nick.String, u.Age.Valid, u.Age.V)
		b, _ := json.Marshal(u)
		fmt.Printf("  json: %s\n", b)
	}

	// 3. Handle NULL in SQL instead.
	var coalesced string
	_ = db.QueryRow(`select coalesce(email, '') from u where id = 2`).Scan(&coalesced)
	fmt.Printf("coalesce(email, ''): %q\n", coalesced)

	// 4. Inserting NULL from Go.
	_, _ = db.Exec(`insert into u (id, name, email) values (3, 'linus', ?)`, sql.NullString{})
	var n int
	_ = db.QueryRow(`select count(*) from u where email is null`).Scan(&n)
	fmt.Printf("rows with NULL email after inserting sql.NullString{}: %d\n", n)
}

go run . 2>&1

plain string: sql: Scan error on column index 0, name "email": converting NULL to string is unsupported
id=1 [email protected] nick.Valid=true nick="countess" age.Valid=true age=36
  json: {"ID":1,"Name":"ada","Email":"[email protected]","Nick":{"String":"countess","Valid":true},"Age":{"V":36,"Valid":true}}
id=2 email=<nil> nick.Valid=false nick="" age.Valid=false age=0
  json: {"ID":2,"Name":"grace","Email":null,"Nick":{"String":"","Valid":false},"Age":{"V":0,"Valid":false}}
coalesce(email, ''): ""
rows with NULL email after inserting sql.NullString{}: 2

The first line is the error. The query select email from u where id = 2 returns a NULL, and scanning it into a string produces:

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

The message names the column and the problem. The fix is to give the NULL somewhere to go.

Four ways to represent NULL

The program scans the same two rows into a struct with three different field types.

A pointer: *string. nil means NULL, anything else points to the value. It is the lightest option, reads naturally (if u.Email != nil) and works with generics and with json: the field marshals as null or as the plain string. The log shows "Email":null for the NULL row. Downsides: you must check for nil before dereferencing, and pointers to scalars allocate.

sql.NullString, sql.NullInt64, sql.NullBool, sql.NullTime and friends. A struct with the value and a Valid flag. u.Nick.Valid is false for NULL, and u.Nick.String is then the empty string. They have been in the standard library from the start and implement Scanner and driver.Valuer, so they also work as arguments when writing. The cost shows in the JSON: the program prints "Nick":{"String":"","Valid":false}, which is not what a client expects. If you serialise these, give them a custom MarshalJSON or convert to another type for the API.

sql.Null[T] (Go 1.22). A generic version for any type: sql.Null[int] has fields V and Valid. It removes the need for a specific type per column type. The JSON is {"V":0,"Valid":false}, with the same drawback as the older types.

COALESCE in SQL. select coalesce(email, '') from u hands Go a string always: the program shows coalesce(email, ''): "". It is the simplest code, and it throws away the difference between NULL and empty. That is acceptable when your domain treats them alike, and it is a bug when NULL means "never provided" and empty means "provided as blank".

The output prints one row for each of the first two, showing nick.Valid=true nick="countess" age.Valid=true age=36 for the filled row and nick.Valid=false nick="" age.Valid=false age=0 for the NULL row. All three field types scanned without error.

Choosing

The strongest answer is on the schema side: make a column NOT NULL whenever the domain does not need absence, with a default if there is a natural one. Every nullable column is a three-valued logic trap in SQL and an extra branch in Go.

Writing NULL

To insert a NULL, pass something that the driver turns into NULL. A nil pointer works: db.Exec(..., (*string)(nil)) stores NULL, because database/sql converts a nil pointer to nil. sql.NullString{} with Valid false works too, and the program confirms it by inserting one and counting rows where email is null; the count is 2 after the insert, one from the original data and the new one. A plain Go "" stores an empty string, not NULL, which is a separate value that where email is null does not match.

For updates, the same applies: update users set email = $1 with a nil pointer clears the field. When a request body can omit a field, decide whether "missing" means "leave unchanged" or "set NULL"; JSON decoding into a pointer cannot tell missing from null by itself. The article on null versus missing JSON fields covers the options.

Comparisons in SQL

NULL is not equal to anything, including itself. where email = null returns no rows; you need where email is null. where email <> 'x' also excludes rows where email is NULL. A query written in a hurry silently drops the NULL rows, and no Go code will tell you. Prefer is distinct from on PostgreSQL (where email is distinct from 'x' treats NULL as a value) or an explicit or email is null.

Aggregates skip NULLs: count(email) counts non-null values, count(*) counts rows. sum over an empty set returns NULL, not 0, so scanning select sum(cents) from orders into an int fails for a customer with no orders; use coalesce(sum(cents), 0).

With the pgx native API

pgx scans NULL into pointers directly (*string) and offers pgtype.Text, pgtype.Int4 and similar types with a Valid field. They behave like the sql.Null family and also marshal sensibly in some cases. For pgx.RowToStructByName, pointer fields are the simplest approach, as shown in the struct scanning article. Whichever you pick, keep it consistent in a package, so the code reads the same everywhere.

Scanning with a loop

The program uses rows.Scan inside a loop over a Rows, which must be closed on every path; the pattern and its failure mode are in the rows article. The scan arguments must match the columns in number and order, and each destination must be a pointer. If you add a column to the query and forget a destination, Scan fails with expected 5 destination arguments in Scan, not 4, which is a good error and an easy one to hit during a refactor. A helper that scans into a struct by column name, such as sqlx or pgx.CollectRows, removes that class of mistake.

Nullable columns and zero values

A common shortcut is to scan NULL into a zero value by using COALESCE for every column and plain Go types. It makes the code short and the model lossy. Three cases show the cost: a nullable deleted_at where NULL means "not deleted" and a zero time.Time is a valid-looking date; a nullable discount_percent where NULL means "no discount" and zero means "discount of zero"; a nullable foreign key where NULL means "no parent" and zero is not a valid ID but would be sent back on update. In each case, a pointer or Null type keeps the distinction, and it is worth a few nil checks.

What to check in review

1. Every nullable column has a plan: pointer, Null type or COALESCE.
2. Aggregates over possibly empty sets use COALESCE.
3. Comparisons that should include NULL rows use IS NULL or IS DISTINCT FROM.
4. API types do not leak sql.NullString into JSON.
5. Writes distinguish empty from NULL on purpose.

The standard library documents the types in the database/sql reference, and the PostgreSQL manual explains comparison with NULL. For the layer above, see scanning rows into structs.