How can I insert complex structs, including JSON arrays, directly into a PostgreSQL database?

Can I Insert a Struct Directly into a PostgreSQL DB?

In PostgreSQL, inserting structs into the database directly is not straightforward if the struct fields are complex or include JSON arrays. However, by utilizing external libraries, you can achieve insert functionality in a more efficient manner.

Solution Using sqlx Library

The sqlx library (github.com/jmoiron/sqlx) provides an elegant solution to this problem. To use it, first tag each struct field with a db tag to specify its database field name.

<code class="go">type ApplyLeave1 struct {
    LeaveId           int       `db:"leaveid"`
    EmpId             string    `db:"empid"`
    SupervisorEmpId   string    `db:"supervisorid"`
    // ... other fields
}</code>

Then, use the NamedExec function in sqlx to insert the entire struct into the database.

<code class="go">import (
    "github.com/jmoiron/sqlx"
    "log"
)

query := `INSERT INTO TABLENAME(leaveid, empid, supervisorid, ...) VALUES(:leaveid, :empid, :supervisorid, ...)`

var leave1 ApplyLeave1

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

_, err = db.NamedExec(query, leave1)
if err != nil {
    log.Fatalln(err)
}</code>

Inserting JSON Arrays

To handle JSON arrays in structs, you can use PostgreSQL's array data type by tagging the field with db:"array".

<code class="go">type CertificateInfo struct {
    Id           int64  `db:"id"`
    FileName     string `db:"filename"`
    FileType     string `db:"filetype"`
    FileLocation string `db:"filelocation"`
}

type ApplyLeave1 struct {
    // ... other fields
    Certificates   []CertificateInfo  `db:"certificates,array"`
}</code>

By following these steps, you can seamlessly insert complex structs, including JSON arrays, into PostgreSQL databases, simplifying your code and enhancing data insertion efficiency.

The above is the detailed content of How can I insert complex structs, including JSON arrays, directly into a PostgreSQL database?. For more information, please follow other related articles on the PHP Chinese website!