postgres throws error: null value in column "id" violates not-null constraint even when value is actually not null

Question

I am building a rest api using grpc,but even though i have value populated i get the above error. The curl Post gets redirected to createAlbum method

i did create a db and table manually in postgres

my db name is album I login using psql album(no password given)

then i created

create Table PHOTO(id INTEGER PRIMARY KEY, albumId INTEGER NOT NULL, title VARCHAR(255) NOT NULL, url VARCHAR(255) UNIQUE NOT NULL, thubmNailUrl VARCHAR(255) UNIQUE NOT NULL);

This is my main code

func main(){
prosgretConname := fmt.Sprintf("dbname=%v user=%v sslmode=disable", "album", "s")
    log.Infof("conname is %s", prosgretConname)
    db, err := gorm.Open("postgres", prosgretConname)
    if err != nil {
        panic("Failed to connect to database!")
    }// connects correctly to db

    //db.Table("photo")
    defer db.Close()
    go startGRPC(db)
    go startHTTP()
    // Block forever
    var wg sync.WaitGroup
    wg.Add(1)
    wg.Wait()

}
func startGRPC(db *gorm.DB) {
    lis, err := net.Listen("tcp", "localhost:5566")
    if err != nil {
        log.Fatalf("failed to listen: %v", err)
    }
    grpcServer := grpc.NewServer()
    srv := svc.NewAlbumServer(db)
    albumgpb.RegisterAlbumServiceServer(grpcServer, srv)
...
}

now my newAlbumServer returns an instance of the db

type Album struct {
    title        string `json:"title"`
    albumid      int    `json:"albumid"`
    id           int    `json:"id" gorm:"primary_key;not null"`
    url          string `json:"url"`
    thumbnailurl string `json:"thumbnailurl"`
}
func (a *AlbumServiceServer) CreateAlbum(ctx context.Context, in *albumgpb.Albumreq) (*albumgpb.Albumreq, error) {
    tx := a.db.Begin()

    photo := Album{}
    photo.title = in.Album.Title
    photo.albumid = int(in.Album.AlbumId)
    photo.id = int(in.Album.Id)
    photo.url = in.Album.Url
    photo.thumbnailurl = in.Album.ThumbNailUrl

    log.Infof("%v", photo.id) // this prints 1
    log.Infof("the id is %v", int(in.Album.Id))// this prints 1 on server logs
    log.Infof("%+v\n", in) //this prints the entire request in the POST command
    //tx.Table("photo").

err := tx.Table("public.photo").Create(&photo).Error // fails in this line stating pq: null value in column "id" violates not-null constraint 

if err != nil {
    log.Errorf("could not insert into db")
    tx.Rollback()
    return nil, err
}

tx.Commit()

....

this is my curl request and response

curl -X POST "http://localhost:8080/album" -d '{"id":1,"albumId":2,"title":"bleh","url":"sds","thumbNailUrl":"123"}'
2020-07-17 14:34:54.641 IST [16074] ERROR:  null value in column "id" violates not-null constraint
2020-07-17 14:34:54.641 IST [16074] DETAIL:  Failing row contains (null, null, null, null, null).
2020-07-17 14:34:54.641 IST [16074] STATEMENT:  INSERT INTO "public"."photo" DEFAULT VALUES RETURNING "public"."photo".*
{"error":"pq: null value in column \"id\" violates not-null constraint","code":2,"message":"pq: null value in column \"id\" violates not-null constraint"}

Any advice on why its failing or what wrong i am doing will really be helpull

I'm sure the reason is the same as described [here](https://stackoverflow.com/a/11129474/720999): all packages based on [reflection](https://golang.org/pkg/reflect)—and `gorm` is supposedly not an excepton—are only able to access exported fields of `struct` types, and yours are not exported. So I'd speculate the ORM code reads no fields from the value of your data type and `id` end up mapped to `NULL`. Can you verify that? — kostix, Jul 17 '20 at 09:17
that mitigated that error, but for some reason now, its changing exported values to underscores ERROR: column "album_id" of relation "photo" does not exist at character 39 2020-07-17 14:59:36.911 IST [17790] STATEMENT: INSERT INTO "public"."photo" ("title","album_id","id","url","thumb_nail_url") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."photo"."id" @kostix — Saiprasanna Sastry, Jul 17 '20 at 09:30
I figured it out , because i was creating the table using AlbumId, for some reason it was mapping it to album_id can any psql guy answer why that is? i am a novice in psql — Saiprasanna Sastry, Jul 17 '20 at 09:35
That's not postgres' doing but gorm's, it's automatically converting camel case to snake case, if you don't want that you probably can change the default through some form of settings, but i'm not sure as i'm not a gorm user, you should visit their web page and documentation to find that out. Alternatively you can probably enforce the column name using struct tags, just like you're doing for json, e.g. `\`json:"albumid" gorm:"albumid"\``, again, i'm not sure this will help, but you can try it if you're in a bind. — mkopriva, Jul 17 '20 at 10:51
... *"why that is?"* because the snake case format is the most commonly used and preferred. — mkopriva, Jul 17 '20 at 10:55
In addition to what mkopriva said, please note that you have `json` tags on all fields of your `Album` struct, but only one of them also has the `gorm` tag. The gorm package is unlikely to ever consider `json` tags. — kostix, Jul 17 '20 at 10:55
Actually this fact about gorm's mapping appoach is stated on the package's docs: — kostix, Jul 17 '20 at 10:57

score 0 · Answer 1 · answered Jul 23 '20 at 17:08

0

Please look at this.

Since the id field is unexported(starts with a lowercase alphabet), it will not be visible to both the json package and gorm.

answered Jul 23 '20 at 17:08

Vaibhav Mishra

415
3
10

postgres throws error: null value in column "id" violates not-null constraint even when value is actually not null

1 Answers1