Go and postgres reinterpreting integer as nil type

Question

I'm trying to get some old code to pass its unit tests. The test that's giving me problems is here:

func Test1(t *testing.T){
 //Code
        testDevice := Device{
                                 ID: 1,
                                 Guid: "C6",
                                 Name: "test device",
                            }  
                         err := Dbmap.Insert(&testDevice)
                         So(err, ShouldBeNil)
 //More code    
}

When I run go test, this returns:

'sql: Scan error on column index 0: converting driver.Value type <nil> ("<nil>") to a int64: invalid syntax'

This is strange because I'm passing it 1, an integer. The device struct is defined here:

type Device struct {
        ID   int64 `db:"id"`
        Guid string
        Name string
}

Here's the schema for the Postgres table it's inserting into:

CREATE TABLE devices(
ID INTEGER,
Guid VARCHAR,
Name VARCHAR
);

I've tried using the sql.NillInt64 type, but that doesn't seem to fix the problem. It feels like somehow, gorp, go, or postgres is interpreting the integer as a nil there. As a final data point, this is all running in various Docker containers, though I don't think this problem is related to Docker. Any insights?

That's been part of the difficulty for me is finding anything about that. From the [Gorp docs](https://godoc.org/github.com/coopernurse/gorp#DbMap.Insert): Insert runs a SQL INSERT statement for each element in list. List items must be pointers. — Errorum, Apr 11 '18 at 23:10
Since the error message says "Scan error" it is likely that not the INSERT itself is the problem. My first guess would be the "last insert if" thing. If memory serves, Postgres doesn't support that, so it makes sense that this would be nil. But I would also expect NullInt* to work then. — Peter, Apr 11 '18 at 23:19
It's looking like this might be a problem with an older version of Gorp. I was using the coopernurse repo which is deprecated. Updating now replaces all the scan errors with `column "Guid" of relation "devices" does not exist`... which is still frustrating but less cryptic. — Errorum, Apr 11 '18 at 23:37
@Peter what are explicit db tags? I've been pretty careful with cases. — Errorum, Apr 11 '18 at 23:58

score 0 · Accepted Answer · answered Apr 13 '18 at 23:53

The answer came down to case sensitivity interaction between Go and postgres. Peter's comment essentially answers the question, but anyone reading this might want more explanation. Go only exports objects that are capitalized, while postgres defaults to interpreting column names as lower case. So I needed structs that were uppercase mapped to lowercase columns (unless I wanted to go through quoting every single uppercase column name). To do this, use tags in your struct, essentially telling Go to call it one thing, while postgres calls it another. Your new structs should looks like this:

type Device struct {
        ID   int64 `db:"id"`
        Guid string `db:"guid"`
        Name string `db:"name"`
}

Now just keep everything lowercase in postgres and you'll be set!

Go and postgres reinterpreting integer as nil type

1 Answers1