'How can I fetch a NULL boolean from Postgres in Go?

Go's zero value for a bool type is false. Postgres supports an undefined BOOL type, represented as NULL. This leads to problems when trying to fetch a BOOL value from Postgres in Go:

rows,err := db.Query("SELECT UNNEST('{TRUE,FALSE,NULL}'::BOOL[])");
if err != nil {
    log.Fatal(err)
}
for rows.Next() {
    var value bool
    if err := rows.Scan(&value); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Value: %t\n",value);
}

Output:

Value: true  
Value: false  
2014/11/17 09:34:26 sql: Scan error on column index 0: sql/driver: couldn't convert <nil> (<nil>) into type bool

What's the most idiomatic way around this problem? The two solutions I have imagined are neither very attractive:

  1. Don't use Go's bool type. Instead I would probably use a string, and do my own conversion which accounts for nil
  2. Always make sure BOOLs are either TRUE or FALSE in Postgres by using COALESCE() or some other means.
postgresqlgoboolean


Solution 1:[1]

My preference is using a pointer:

for rows.Next() {
    var value *bool

    if err := rows.Scan(&value); err != nil {
        log.Fatal(err)
    }

    if value == nil {
        // Handle nil
    }

    fmt.Printf("Value: %t\n", *value);
}

Solution 2:[2]

See http://golang.org/pkg/database/sql/#NullBool in the standard library.

NullBool represents a bool that may be null. NullBool implements the Scanner interface so it can be used as a scan destination, similar to NullString.

Solution 3:[3]

In case of using SQL + graphQL (gqlgen) my preference is using pointers as well.

Why?

Because when I generate a schema it will have pointers on the output and this is much easy to connect with a relevant struct which contains pointers (which handle DB operations).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Martin Gallagher
Solution 2 Dmitri Goldring
Solution 3 Honeywild

Related Questions