I am trying to insert/update data in PostgreSQL using jackc/pgx into a table that has column of composite 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"`
}
The column history is an array of composite type which I have defined as DayPriceModel in PSQL. I want to append a new element to history in golang using jack/pgx
I have so far written the given code:
// The code for newType was generated by ChatGPT so it might or might not be correct. Feel free to overwrite this part.
newType, _ := pgtype.NewCompositeType("day_price_model", []pgtype.CompositeTypeField{
{Name: "date", OID: pgtype.DateOID},
{Name: "high", OID: pgtype.Float4OID},
{Name: "low", OID: pgtype.Float4OID},
{Name: "open", OID: pgtype.Float4OID},
{Name: "close", OID: pgtype.Float4OID},
}, (*pgtype.ConnInfo)(pool.Config().ConnConfig.TLSConfig.ClientCAs))
_, err = pool.Exec(context.Background(), `UPDATE equity.securities_price_history
SET history = $1::equity.day_price[] || history WHERE symbol = $2`,
composite_value_here, "something") // unable to form the composite_value_here variable
Using jack/pgx, how do I create a new composite value from a composite type to write in the PSQL query.
Requirements
psqlgithub.com/jackc/pgx/v5The 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.