在Postgresql中存储和检索Golang slice，struct和struct slice

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
}

Answer 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.