How can I avoid a pair of scan: <nil> * json.RawMessage errors for null JSON columns?

Question

How can I avoid a pair of scan: <nil> * json.RawMessage errors for null JSON columns?

A few weeks ago I started learning and trying to create a simple blogging program while learning the basics.

I am currently trying to find and save blog posts using packages database/sql

and github.com/lib/pq

. I don't prefer using third party packages like sqlx

or gorm

without fully understanding go's own behavior and basics.

My Post

struct looks something like this:

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

When saving messages, my function save()

works without issue:

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 little 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 it hits a columnar row whose tag column is null and I get the following error at this point:

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

My question is, what is the correct way to handle columns nullable json

when scanning a result set? lib/pq

Is this error related to ?

My circuit:

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 the content of the persisted (non-null) field tags

with already saved:

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

Any ideas?

+3

json sql go

edigu Apr 17 15 at 18:23

source to share

1 answer

Dave c · Answer 1 · 2015-04-17T20:03:04+0000

TL; DR: change your structure to Tags *json.RawMessage

or use a temporary value of this type.

Note that you can search the Go source for the bug report to get a better understanding of what's going on behind the scenes if you're interested (standard packages are basically a good source for well-written Go code).

I tested a new 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 is probably better to give commands to re-create or whatever is used to create a table rather than using a form that cannot be directly used. Also when I was familiar with PostgreSQL I remember that it was extremely rare, that the column was varchar

not a bug, not just used text

, perhaps with a length constraint.)

Using this table with your struct type I got:

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

Inset. Going to []byte(p.Tags)

made it go away (but see below) and then the request worked the same as it did for you.

I got the same error as when querying when I entered NULL into the table. The solution to this was to change the field of the structure to Tags *json.RawMessage

. Then I can remove the addition I added to insert and the query worked fine, either setting the field to nil

or not matching.

If you do, be sure to check if there is one item.Tags

nil

before using it. Also, create a database field NOT NULL

.

I'm not too familiar with Go's database support to know if it is reasonable to require a pointer to slice to handle NULLs; I would hope that since Go already distinguishes between empty slice and null slice.

Alternatively, you can leave your type "as is" and use a temporary expression 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 will probably have a similar problem if you test for a NULL body. Either create a database column NOT NULL

or use sql.NullString

either in your type or as temporary as above (using a field Valid

NullString

to see if the row needs to be copied).

A few other small notes:

golint

suggests using ID

instead ID

.
You don't provide your implementation GetDB

, I hope it just gets shared / global *sql.DB

. You don't want to re-name sql.Open

.
In both of your functions, you can use a prepared statement (created outside of those functions). This can help if you call these functions frequently.
Your query is trying to use the "posts" table / view instead of "post"; Am I guessing a typo while you superimpose the question?
getLatestPosts

Reutrns function *[]Post

; do not do that. Just return []Post

. You almost never want to use a pointer to a slice and certainly not as a return type.

How can I avoid a pair of scan: <nil> * json.RawMessage errors for null JSON columns?

More articles: