Storing and retrieving Golang slice, struct and slice of struct in Postgresql

Question

Let say I have a table named PEOPLE which store rows of Person. However, the Person contains slice, struct and slice of struct, i.e. the Superpower, Vehicle and FriendList fields respectively.

How should I store and retrieve those values in Postgresql with Golang? I can't find any practical tutorial related to this problem. Use custom data types, or JSON, or ORM method, or building custom functions to deal with these? Many opinions out there, but I still can't figure out a working way to solve this problem, please help.

I am using Ozzo-dbx , however, any solutions is welcome.

Can someone provides a working example? Sorry if this newbie question irritates you, but I already tried for few days, and keep failing... (T_T)

type Transport struct {
    Brand string
    Size  int
}

type Friend struct {
    Name         string
    IsProgrammer bool
}

type Person struct {
    ID         int
    Name       string
    Superpower []string   //slice
    Vehicle    Transport  //struct
    FriendList []Friend   //slice of struct
}

You can store slice, struct and slice of struct values in JSON format, and other values as is (so ID as int, Name as string etc). It will be logical :) — V. Panchenko
– V. Panchenko, Commented Dec 8, 2017 at 16:44
How are the SuperPower, Vehicle, and FriendList properties stored in the database? Do you control that decision or are there related tables for them? If it's up to you then you have many choices; if the database structure is already set then you just have to handle those associations... — maerics
– maerics, Commented Dec 8, 2017 at 16:46
I wish to store those fields (SuperPower, Vehicle, and FriendList) as if the data types in Golang. This is the easiest. Hopefully, those fields can be stored without JSON encoding, then that is the fastest. If it is possible to store without relative tables with association, than that will be elagant. If possible, I wish to access the database with dot notation, such as SELECT Person.Vehicle.Brand ..., then this may beautifully implemented... is it possible? :) — Boo Yan Jiong
– Boo Yan Jiong, Commented Dec 8, 2017 at 17:06

mkopriva · Accepted Answer · 2017-12-08 17:13:55Z

1

In most cases you should create separate tables for Superpowers, Vehicles, and Friends.

If the relationships between one of these tables and the People table is many-to-one or one-to-one you can add foreign keys to them. If the relationship is many-to-many you should create a linking table that will hold the fkeys of the related records.

To store the data you can insert/update each record individually with simple SQL query strings and database/sql.

After that you can create table views that would aggregate the data you want per one person into a single row so that you don't pollute your Go code with complex SQL strings. Then you define simple SQL select query strings in Go and execute them with database/sql.

answered Dec 8, 2017 at 17:13

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Boo Yan Jiong Over a year ago

Yeah, thank you for your suggestion... Hmmm... the website front-end uses too much nested object as that will ease their workflow... Nested objects and arrays are everywhere... Haha... I read about custom data types in Postgresql, hope that will be my saviour... Hmmm... Anyway, thank you for your suggestion... will try to implement the relationship table if can't find any better solution :)

Collectives™ on Stack Overflow

Storing and retrieving Golang slice, struct and slice of struct in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related