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

Question

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 inserting data:

    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

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 preferred but database/SQL would just do fine

See: Array, Composite, and Enum Type Registration and Extending Existing Type Support and Entirely New Type Support — mkopriva
– mkopriva, Commented Feb 4, 2023 at 4:44
However the more straightforward way, it appears to me, for supporting custom composite types, would be to simply implement the driver.Valuer interface, which pgx supports, and have the implementation produce a valid "literal constant" as documented here: postgresql.org/docs/15/interactive/rowtypes.html#id-1.5.7.24.6 — mkopriva
– mkopriva, Commented Feb 4, 2023 at 4:50
I have tried using LoadType but don't know how to move further. This is kind of a complicated case as there is not only a custom type but an array of custom types that need to be updated in the DB. I would be really grateful for a small code snippet. Thanks!! — Krushnal Patel
– Krushnal Patel, Commented Feb 4, 2023 at 7:02

mkopriva · Accepted Answer · 2023-02-04 09:09:06Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related