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

Question

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:

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

Dmitri Goldring · Accepted Answer · 2014-11-17 16:18:40Z

8

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.

answered Nov 17, 2014 at 16:18

Dmitri Goldring

4,3832 gold badges26 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Gallagher · Accepted Answer · 2014-11-17 17:00:36Z

5

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);
}

answered Nov 17, 2014 at 17:00

Martin Gallagher

4,8842 gold badges32 silver badges28 bronze badges

Comments

Honeywild · Accepted Answer · 2022-04-18 13:43:23Z

0

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).

answered Apr 18, 2022 at 13:43

Honeywild

213 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related