How to avoid Scan pair: <nil> *json.RawMessage errors on nullable JSON columns?

Several weeks ago I started to learn go and trying to build a simple blogging application while learning the basics.

Currently I'm trying to fetch and persist blog posts using database/sql and github.com/lib/pq packages. I dont prefer utilizing 3rd party packages like sqlx or gorm without fully understand the native behaviours and basics of the go.

My Post struct is something like this:

type Post struct {
    Id        int
    Title     string
    Body      string
    Tags      json.RawMessage
}

When persisting posts, my save() function works without any problem:

func (p *Post) save() (int, error) {
    const query = `INSERT INTO post (title, body, tags) VALUES($1, $2, $3) RETURNING id`
    db := GetDB()
    var postid int
    err := db.QueryRow(query, p.Title, p.Body, p.Tags).Scan(&postid)
    return postid, err
}

and to read the latest posts, I wrote a small function:

func getLatestPosts(page int) (*[]Post, error) {
    const query = `SELECT id, title, body, tags FROM posts ORDER BY id DESC LIMIT 10`
    var items []Post
    db := GetDB()
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }

    for rows.Next() {
        var item Post
        if err := rows.Scan(&item.Id, &item.Title, &item.Body, &item.Tags); err != nil {        
            log.Fatal(err)
        }
        items = append(items, item)
    }
    return &items, err
}

This also works until hitting a post row which tags column is null and I'm getting the following error at this point:

> 2015/04/16 21:53:04 sql: Scan error on column index 4: unsupported
> driver -> Scan pair: <nil> -> *json.RawMessage

My question is, what is the proper way to handle nullable json columns while scanning the resultset? Is this error related with the lib/pq?

My schema is:

CREATE TABLE post (
    "id" int4 NOT NULL DEFAULT nextval('post_id_seq'::regclass),
    "title" varchar(255) NOT NULL COLLATE "default",
    "body" text COLLATE "default",
    "tags" json,
    PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE
);

and here is an already persisted (not-null) tags field content:

 {
     "black-and-white" : "Black and White",
     "the-godfather" : "The Godfather"
 }

Any ideas?

TL;DR: change your struct to have Tags *json.RawMessage or use a temporary of that type.

Note you can search the Go source for the error message to get a better idea of what's going on behind the scenes if you're interested (the standard packages are mostly a good source of well written Go code).

I tested against a fresh PostgreSQL-9.4.1 server with the following table:

CREATE TABLE post (
        "id"    serial          PRIMARY KEY,
        "title" varchar(255)    NOT NULL,
        "body"  text,
        "tags"  json
);

(by the way, it's probably better to give the commands to re-create, or that where used to create the table rather than the form use gave which can't directly be used. Also, when I was familiar with PostgreSQL I recall it was exceedingly rare that a varchar column wasn't a mistake instead of just using text, possibly with a length constraint.)

Using that table with your struct type I got:

converting Exec argument #2's type: unsupported type json.RawMessage, a slice

On the insert. Changing to []byte(p.Tags) made that go away (but see below) and then querying worked as you had it.

I only got the same error you did on querying when I put a NULL value into the table. The solution to this was to change the struct field to Tags *json.RawMessage. I could then remove the cast I added on insert and and the query worked fine, either setting the field to nil or not as appropriate.

If you do this, don't forget to check if item.Tags is nil before using it. Alternatively, make the database field NOT NULL.

I'm not overly familiar with Go's database support to know if requiring a pointer to a slice to handle NULLs is reasonable; I'd have hoped not since Go already distinguishes between an empty slice and a nil slice.

Alternatively, you can leave your type as-is and use a temporary like this:

    var item post
	var tmp *json.RawMessage
	if err := rows.Scan(&item.Id, &item.Title, &item.Body, &tmp); err != nil {
		log.Fatal(err)
	}
	if tmp != nil {
		item.Tags = *tmp
	}

You'll likely experience a similar issue when you test with a NULL body. Either make the database column NOT NULL or use sql.NullString, either in your type or as a temporary as above (using the Valid field of NullString to see if you should copy the string).

A few other minor notes:

• golint suggests using ID instead of Id.
• You don't provide your GetDB implementation, I hope it's just getting a shared/global *sql.DB. You don't want to call sql.Open repeatedly.
• In both of your functions you could use a pre-prepared statement (created once outside those functions). That might help if you call those functions often.
• Your query tries to use the "posts" table/view instead of "post"; I'm guessing a typo while cut-n-pasting to the question?
• Your getLatestPosts function reutrns *[]Post; don't do that. Just return []Post. You almost never want to use a pointer to a slice, and certainly not as a return type.