The pgx Files 08: Handing Nulls

21 Aug 2016

Welcome back to The pgx Files! Today we learn about handling nulls. As we know, Go's nil is the zero value for pointers, but the zero value for primitives like int and string are 0 and ''; there is no nil. So how do we transfer nulls from PostgreSQL data types that are allowed to have nulls, to Go types like int and string that are not allowed to have nil?

The short answer is, don't. SQL's coalesce allows us to transform nulls to more useful values anyway.

Let's say we have the following table with the following values.

# create table sales(
    salesperson text null,
    sales int null);

# insert into sales (salesperson) values ('Bob');
# insert into sales (sales) values (21);

# \pset null '[NULL]'

# select * from sales;
┌─────────────┬────────┐
│ salesperson │ sales  │
├─────────────┼────────┤
│ Bob         │ [NULL] │
│ [NULL]      │     21 │
└─────────────┴────────┘
(2 rows)

If we decide there are sane values for the nulls, we just use coalesce to provide those values, and our client can read them in to data types that don't support null/nil:

# select coalesce(salesperson, 'nobody') as salesperson,
         coalesce(sales,        0)       as sales
    from sales;
┌─────────────┬───────┐
│ salesperson │ sales │
├─────────────┼───────┤
│ Bob         │     0 │
│ nobody      │    21 │
└─────────────┴───────┘
(2 rows)

SQL fans know that many data problems can be solved in the database before even reaching the client.

For those times when you really do need to return nulls to the client, pgx provides data types that can be asked if they were null or not.

package main

import (
	"fmt"
	"log"

	"github.com/jackc/pgx"
	"github.com/manniwood/playground/pgxfiles"
)

func main() {
	conn := util.Connect("null selecter")
	defer conn.Close()

	var salesperson pgx.NullString
	var sales pgx.NullInt32
	rows, err := conn.Query(`
	select salesperson,
	       sales
	  from sales`)
	if err != nil {
		log.Fatalf("Unexpected error trying to read sales: %v\n", err)
	}
	defer rows.Close()

	for rows.Next() {
		rows.Scan(&salesperson, &sales)
		fmt.Printf("Salesperson %s, (null? %t); sales: %d (null? %t)\n",
			salesperson.String, !salesperson.Valid, sales.Int32, !sales.Valid)
	}
}

The output of the above program on the above table:

Salesperson Bob, (null? false); sales: 0 (null? true)
Salesperson , (null? true); sales: 21 (null? false)

The idea is that pgx.NullString has two fields, String and Valid. Valid is set to true when the String field is '' and was also '' in PostgreSQL. Conversely, Valid is set to false when the String field is '' but was actually null in PostgreSQL.

pgx.NullInt32 works the same way, except its value field is named Int32, and of course its zero value is 0 (not the empty string). The Valid field works the same.

The complete list of pgx.Null* data types can be found at https://godoc.org/github.com/jackc/pgx.

Of course, now we need to test inserting as well!

package main

import (
	"fmt"
	"log"

	"github.com/jackc/pgx"
	"github.com/manniwood/playground/pgxfiles"
)

func main() {
	conn := util.Connect("null inserter")
	defer conn.Close()

	result, err := conn.Exec(`
	insert into sales (salesperson, sales)
	           values ($1, $2),
	                  ($3, $4)`,
		pgx.NullString{String: "Chuck", Valid: true},
		pgx.NullInt32{Int32: 0, Valid: false},
		pgx.NullString{String: "", Valid: false},
		pgx.NullInt32{Int32: 23, Valid: true})
	if err != nil {
		log.Fatalf("Unexpected error trying to insert into sales: %v\n", err)
	}

	fmt.Printf("Inserted %d rows into sales\n", result.RowsAffected())
}

When we run this on the command line:

$ ./null_insert 
Inserted 2 rows into sales

And, when we look at the table from a psql session:

# select * from sales; rollback;
┌─────────────┬────────┐
│ salesperson │ sales  │
├─────────────┼────────┤
│ Bob         │ [NULL] │
│ [NULL]      │     21 │
│ Chuck       │ [NULL] │
│ [NULL]      │     23 │
└─────────────┴────────┘
(4 rows)

Nice.

For inserts, pgx also knows how to deal with nils directly, like so:

package main

import (
	"fmt"
	"log"

	"github.com/manniwood/playground/pgxfiles"
)

func main() {
	conn := util.Connect("null inserter")
	defer conn.Close()

	result, err := conn.Exec(`
	insert into sales (salesperson, sales)
	           values ($1, $2), ($3, $4)`,
		"Alice",
		nil,
		nil,
		42)
	if err != nil {
		log.Fatalf("Unexpected error trying to insert into sales: %v\n", err)
	}

	fmt.Printf("Inserted %d rows into sales\n", result.RowsAffected())
}

# select * from sales;
┌─────────────┬────────┐
│ salesperson │ sales  │
├─────────────┼────────┤
│ Bob         │ [NULL] │
│ [NULL]      │     21 │
│ Chuck       │ [NULL] │
│ [NULL]      │     23 │
│ Alice       │ [NULL] │
│ [NULL]      │     42 │
└─────────────┴────────┘
(6 rows)

And, for reading back out, pointers to types (rather than using the pgx.Null* types) also works:

package main

import (
	"fmt"
	"log"

	"github.com/manniwood/playground/pgxfiles"
)

func main() {
	conn := util.Connect("null selecter")
	defer conn.Close()

	var salesperson *string
	var sales *int
	rows, err := conn.Query(`
	select salesperson,
	       sales
	  from sales`)
	if err != nil {
		log.Fatalf("Unexpected error trying to read pg_stat_activity: %v\n", err)
	}
	defer rows.Close()

	salespersonValid := false
	salesValid := false
	displaySalesperson := ""
	displaySales := 0
	for rows.Next() {
		salespersonValid = false
		salesValid = false
		displaySalesperson = ""
		displaySales = 0
		rows.Scan(&salesperson, &sales)
		if salesperson != nil {
			salespersonValid = true
			displaySalesperson = *salesperson
		}
		if sales != nil {
			salesValid = true
			displaySales = *sales
		}
		fmt.Printf("Salesperson %s, (null? %t); sales: %d (null? %t)\n",
			displaySalesperson, !salespersonValid, displaySales, !salesValid)
	}
}

Next on the pgx files: Stored procedures!