Hidden goodies inside lib/pq

It has happened to all of us. You get into a habit and accept a few inconveniences and move on. It bothers you, but you procrastinate, putting it in the backburner by slapping that mental TODO note. Yet surprisingly, sometimes the solution is right in front of you.

Take my case. I have always done _ "github.com/lib/pq" in my code to use the postgres driver. The _ is to register the driver with the standard library interface. Since we usually do not actually use the pq library, one needs to use a _ to import the library without exposing the package in the code. Life went on and I didn’t even bother to look for better ways to do things. Until the time came and I screamed “There has to be a better way !”.

Indeed there was. It was the actual pq package, which I was already using but never actually imported ! Yes, I am shaking my head too . Stupidly, I had always looked at database/sql and never bothered to look at the underlying lib/pq package. Oh well, dumb mistakes are bound to happen. I learn from them and move on.

Let’s take a look at some of the goodies that I found inside the package, and how it made my postgres queries look much leaner and elegant.

Arrays

Let’s say that you have a table like this -

CREATE TABLE IF NOT EXISTS users (
	id serial PRIMARY KEY,
	comments text[]
);

Believe it or not, for the longest time, I did this to scan a postgres array -

id := 1
var rawComments string
err := db.QueryRow(`SELECT comments from users WHERE id=$1`, id).Scan(&rawComments)
if err != nil {
	return err
}
comments := strings.Split(rawComments[1:len(rawComments)-1], ",")
log.Println(id, comments)

It was ugly. But life has deadlines and I moved on. Here is the better way -

var comments []string
err := db.QueryRow(`SELECT comments from users WHERE id=$1`, id).Scan(pq.Array(&comments))
if err != nil {
	return err
}
log.Println(id, comments)

Similarly, to insert a row with an array -

id := 3
comments := []string{"marvel", "dc"}
_, err := db.Exec(`INSERT INTO users VALUES ($1, $2)`, id, pq.Array(comments))
if err != nil {
	return err
}

Null Time

Consider a table like this -

CREATE TABLE IF NOT EXISTS last_updated (
	id serial PRIMARY KEY,
	ts timestamp
);

Now if you have an entry where ts is NULL, it is extremely painful to scan it in one shot. You can use coalesce or a CTE or something of that sort. This is how I would have done it earlier -

id := 1
var ts time.Time
err := db.QueryRow(`SELECT coalesce(ts, to_timestamp(0)) from last_updated WHERE id=$1`, id).Scan(&ts)
if err != nil {
	return err
}
log.Println(id, ts, ts.IsZero()) // ts.IsZero will still be false btw !

This is far better -

id := 1
var ts pq.NullTime
err := db.QueryRow(`SELECT ts from last_updated WHERE id=$1`, id).Scan(&ts)
if err != nil {
	return err
}
if ts.Valid {
	// do something
}
log.Println(id, ts.Time, ts.Time.IsZero()) // This is true !

Errors

Structured errors are great. But the only error type check that I used to have in my tests were for ErrNoRows since that is the only useful error type exported by the database/sql package. It frustrated me to no end. Because there are so many types of DB errors like syntax errors, constraint errors, not_null errors etc. Am I forced to do the dreadful string matching ?

I made the discovery when I learnt about the # format specifier. Doing a t.Logf("%+v", err) versus t.Logf("%#v", err) makes a world of a difference.

If you have a key constraint error, the first would print

pq: duplicate key value violates unique constraint "last_updated_pkey"

whereas in case of latter

&pq.Error{Severity:"ERROR", Code:"23505", Message:"duplicate key value violates unique constraint \"last_updated_pkey\"", Detail:"Key (id)=(1) already exists.", Hint:"", Position:"", InternalPosition:"", InternalQuery:"", Where:"", Schema:"public", Table:"last_updated", Column:"", DataTypeName:"", Constraint:"last_updated_pkey", File:"nbtinsert.c", Line:"433", Routine:"_bt_check_unique"}

Aha. So there is an underlying pq.Error type. And it has error codes ! Wohoo ! Better tests !

So in this case, the way to go would be -

pqe, ok := err.(*pq.Error)
if ok != true {
	t.Fatal("unexpected type")
}
if string(pqe.Code) != "23505" {
	t.Error("unexpected error code.")
}

And that’s it ! For a more detailed look, head over to the package documentation.

Feel free to post a comment if you spot a mistake. Or if you know of some other hidden gems, let me know !