Question 193772 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Golang: insert nested structure model into Postgres in

i have this model data structure:

type Order struct {
    OrderEntry OrderEntry `db:"order"`
}

type OrderEntry struct {
    Order_uid          string
    Track_number       string
    Entry              string
    Delivery           Delivery
    Payment            Payment
    Items              []Item
    Locale             string
    Internal_signature string
    Customer_id        string
    Delivery_service   string
    Shardkey           string
    Sm_id              int64
    Date_created       string
    Oof_shard          string
}

type Delivery struct {
    ...
}

type Payment struct {
    ...
}

type Item struct {
    ...
}

And i have table

CREATE TABLE "order"
(
    "order" jsonb NOT NULL
);

How do i insert Order object to this psq table? I use sqlx and for this code im getting error

func (r *RepositoryPostgres) CreateDocument(order L0.Order) error {
    tx := r.db.MustBegin()
    tx.MustExec("INSERT INTO order (order) VALUES ($1)", order.OrderEntry)
    err := tx.Commit()

    if err != nil {
        return err
    }

    return nil
}

panic: sql: converting argument $1 type: unsupported type L0.OrderEntry, a struct

How to properly fix this? Thanks

Tags:

2

Answers


  1. 0

    The INSERT statement’s unsupported type "L0.OrderEntry" is the cause of the issue you’re experiencing. "L0.OrderEntry" cannot be directly entered into the JSONB column since it is a nested struct within "L0.Order". Instead, before entering the OrderEntry object into the table, you must marshal it into a JSON representation.

    You can change your code as below to resolve the error.

        import "encoding/json"

    func (r *RepositoryPostgres) CreateDocument(order L0.Order) error {
    tx := r.db.MustBegin()

    orderJSON, error := json.Marshal(order.OrderEntry)
    if error != nil {
      return error
    }

    tx.MustExec("INSERT INTO order (order) VALUES ($1)", orderJSON)
    error = tx.Commit()

    if error != nil {
     return error
    }

     return nil
    }

    The order.OrderEntry object is marshalled into JSON in the modified code using the "json.Marshal" method. The generated JSON byte slice (orderJSON) is then provided to the INSERT statement as the value.

    You may make sure the data is in the correct format for insertion into the JSONB column by marshalling the nested struct into JSON.

    Login or Signup to reply.
  2. 0

    There are a couple of changes you need to make here.

    1. You need to marshal to JSON the struct you want to store. You can do that with:
    orderB, err := json.Marshal(order.OrderEntry)
if err != nil {
    // handle err
}

tx.MustExec("INSERT INTO order (order) VALUES ($1)", orderB)

    This will now store your struct as a JSON byte-array in the table.

    1. In order to generate JSON from your struct correctly, you need to add JSON struct tags to each field. e.g.:
    type OrderEntry struct {
    Order_uid          string `json:"order_uid"`
    Track_number       string `json:"track_number"`
    Entry              string `json:"entry"`
    Delivery           Delivery `json:"delivery"`
    Payment            Payment `json:"payment"`
    Items              []Item `json:"items"`
    Locale             string `json:"locale"`
    Internal_signature string `json:"internal_signature"`
    Customer_id        string `json:"customer_id"`
    Delivery_service   string `json:"delivery_service"`
    Shardkey           string `json:"shard_key"`
    Sm_id              int64 `json:"sm_id"`
    Date_created       string `json:"date_created"`
    Oof_shard          string `json:"oof_shard"`
}

    You can name these however you like, snake-case being the common format. These tags provide the names for the JSON fields to the parser.

    Without these fields, you’ll end up with empty JSON, and you won’t be able to retrieve the data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search