Unable to INSERT/UPDATE data with custom type in postgresql using golang-CodePudding

I am trying to insert/update data in PostgreSQL using jackc/pgx into a table that has column of custom type. This is the table type written as a golan struct:

// Added this struct as a Types in PSQL
type DayPriceModel struct {
    Date                time.Time `json:"date"`
    High                float32   `json:"high"`
    Low                 float32   `json:"low"`
    Open                float32   `json:"open"`
    Close               float32   `json:"close"`
}

// The 2 columns in my table
type SecuritiesPriceHistoryModel struct {
    Symbol  string          `json:"symbol"`
    History []DayPriceModel `json:"history"`
}

I have written this code for insertion:

    func insertToDB(data SecuritiesPriceHistoryModel) {
        DBConnection := config.DBConnection
        _, err := DBConnection.Exec(context.Background(), "INSERT INTO equity.securities_price_history (symbol) VALUES ($1)", data.Symbol, data.History)
    }

But I am unable to insert the custom data type (DayPriceModel).

I am getting an error like this failed to encode args[1]: unable to encode (The error is very long and mostly shows my data so I have picked out the main part.

How do I INSERT data into PSQL with such custom data types?

PS: An implementation using jackc/pgx is preffered but database/sql would just do fine

CodePudding user response：

I'm not familiar enough with pgx to know how to setup support for arrays of composite types. But, as already mentioned in the comment, you can implement the driver.Valuer interface and have that implementation produce a valid literal, this also applies if you are storing slices of structs, you just need to declare a named slice and have that implement the valuer, and then use it instead of the unnamed slice.

// named slice type
type DayPriceModelList []DayPriceModel

// the syntax for array of composites literal looks like
// this: '{"(foo,123)", "(bar,987)"}'. So the implementation
// below must return the slice contents in that format.
func (l DayPriceModelList) Value() (driver.Value, error) {
    // nil slice? produce NULL
    if l == nil {
        return nil, nil
    }
    // empty slice? produce empty array
    if len(l) == 0 {
        return []byte{'{', '}'}, nil
    }

    out := []byte{'{'}
    for _, v := range l {
        // This assumes that the date field in the pg composite
        // type accepts the default time.Time format. If that is
        // not the case then you simply provide v.Date in such a
        // format which the composite's field understand, e.g.,
        // v.Date.Format("<layout that the pg composite understands>")
        x := fmt.Sprintf(`"(%s,%f,%f,%f,%f)",`,
            v.Date,
            v.High,
            v.Low,
            v.Open,
            v.Close)
        out = append(out, x...)
    }
    out[len(out)-1] = '}' // replace last "," with "}"
    return out, nil
}

And when you are writing the insert query, make sure to add an explicit cast right after the placeholder, e.g.

type SecuritiesPriceHistoryModel struct {
    Symbol  string            `json:"symbol"`
    History DayPriceModelList `json:"history"` // use the named slice type
}

// ...

_, err := db.Exec(ctx, `INSERT INTO equity.securities_price_history (
    symbol
    , history
) VALUES (
    $1
    , $2::my_composite_type[])
`, data.Symbol, data.History)

// replace my_composite_type with the name of the composite type in the database

NOTE#1: Depending on the exact definition of your composite type in postgres the above example may or may not work, if it doesn't, simply adjust the code to make it work.

NOTE#2: The general approach in the example above is valid, however it is likely not very efficient. If you need the code to be performant do not use the example verbatim.