Requirements
- composite type is defined using
psql
- updating/inserting into a column of type array of composite type
- using
github.com/jackc/pgx/v5
The easiest way is to use pgx.LoadType()/pgx.RegisterType() and use the composite type already defined in the db.
Since we have an array of composite type we need to do it for both, the composite type itself and the array type (you can check with select 'day_price_model'::regtype::oid; respectively select 'day_price_model[]'::regtype::oid; that the oids are different).
For registration we can take RegisterDataTypes from the v5/pgtype documentation.
Assuming the type was created with psql as follows:
create type day_price_model as (
date date,
high float,
low float,
open float,
close float
);
the RegisterDataTypes could look sth like:
func RegisterDataTypes(ctx context.Context, conn *pgx.Conn) error {
dataTypeNames := []string{
"day_price_model",
"day_price_model[]",
}
for _, typeName := range dataTypeNames {
dataType, err := conn.LoadType(ctx, typeName)
if err != nil {
return err
}
conn.TypeMap().RegisterType(dataType)
}
return nil
}
Note the above data type names for both types.
Give some mock data:
history := []DayPriceModel{
{time.Now().AddDate(0, 0, -2), 4, 1, 2, 3},
{time.Now().AddDate(0, 0, -1), 10, 5, 6, 7},
}
an insert would be simply:
insertStmt := `INSERT INTO securities_price_history VALUES ($1, $2)`
_, err = conn.Exec(context.Background(), insertStmt, "something", history)
and the update something like this:
updateStmt := `UPDATE securities_price_history SET history = $1 WHERE symbol = $2`
_, err = conn.Exec(context.Background(), updateStmt, newHistory, "something")
Test
In order to have a complete, self-contained example of a test, we need a db and a small test program, taking into account the points mentioned above.
DB
A test DB using psql could be created like this:
create database equity;
\c equity
create type day_price_model as (
date date,
high float,
low float,
open float,
close float
);
create table securities_price_history (
symbol varchar,
history day_price_model[]
);
Go Program
package main
import (
"context"
"fmt"
"github.com/jackc/pgx/v5"
_ "github.com/jackc/pgx/v5"
_ "github.com/jackc/pgx/v5/stdlib"
"log"
"time"
)
type DayPriceModel struct {
Date time.Time `json:"date"`
High float32 `json:"high"`
Low float32 `json:"low"`
Open float32 `json:"open"`
Close float32 `json:"close"`
}
type SecuritiesPriceHistoryModel struct {
Symbol string `json:"symbol"`
History []DayPriceModel `json:"history"`
}
func RegisterDataTypes(ctx context.Context, conn *pgx.Conn) error {
dataTypeNames := []string{
"day_price_model",
"day_price_model[]",
}
for _, typeName := range dataTypeNames {
dataType, err := conn.LoadType(ctx, typeName)
if err != nil {
return err
}
conn.TypeMap().RegisterType(dataType)
}
return nil
}
func main() {
dsn := "host=localhost port=5432 user=postgres password=postgres dbname=equity"
conn, err := pgx.Connect(context.Background(), dsn)
if err != nil {
log.Fatal(err)
}
defer conn.Close(context.Background())
err = RegisterDataTypes(context.Background(), conn)
if err != nil {
log.Fatal(err)
}
history := []DayPriceModel{
{time.Now().AddDate(0, 0, -2), 4, 1, 2, 3},
{time.Now().AddDate(0, 0, -1), 10, 5, 6, 7},
}
insertStmt := `INSERT INTO securities_price_history VALUES ($1, $2)`
_, err = conn.Exec(context.Background(), insertStmt, "something", history)
if err != nil {
log.Fatal(err)
}
sphm := &SecuritiesPriceHistoryModel{}
selectStmt := `SELECT (symbol, history) FROM securities_price_history WHERE symbol=$1`
err = conn.QueryRow(context.Background(), selectStmt, "something").Scan(sphm)
if err != nil {
log.Fatal(err)
}
fmt.Printf("after insert: %v\n", sphm)
newHistory := append(history, DayPriceModel{time.Now(), 6, 3, 4, 5})
updateStmt := `UPDATE securities_price_history SET history = $1 WHERE symbol = $2`
_, err = conn.Exec(context.Background(), updateStmt, newHistory, "something")
if err != nil {
log.Fatal(err)
}
err = conn.QueryRow(context.Background(), selectStmt, "something").Scan(sphm)
if err != nil {
log.Fatal(err)
}
fmt.Printf("after update: %v\n", sphm)
}
The output of the test program is:
after insert: &{something [{2023-03-10 00:00:00 +0000 UTC 4 1 2 3} {2023-03-11 00:00:00 +0000 UTC 10 5 6 7}]}
after update: &{something [{2023-03-10 00:00:00 +0000 UTC 4 1 2 3} {2023-03-11 00:00:00 +0000 UTC 10 5 6 7} {2023-03-12 00:00:00 +0000 UTC 6 3 4 5}]}
As you can see, a record has been inserted, the update has been performed successfully, because there are then three elements in the array and the data can be read from the DB.
If you then want to repeat the attempt, you should obviously remove the data from the table again, e.g. with psql. However, this should only be as small an example as possible to run an example with a local db.
