Description
An unable to encode []struct into binary format for text (OID 25) error is occurring when using pgxpool.CopyForm to copy a nested struct.
Using:
entries = append(entries, []any{dailycves.Cve.ID, dailycves.Cve.SourceIdentifier, dailycves.Cve.Published, dailycves.Cve.LastModified, dailycves.Cve.Descriptions})
And then trying to copy using pgxpool.CopyForm the error is:
Error copying into dailycves table: ERROR: COPY from stdin failed: unable to encode []struct { Lang string "json:\"lang\""; Value string "json:\"value\"" }{struct { Lang string "json:\"lang\""; Value string "json:\"value\"" }{Lang:"en", Value:"<informational text>"}, struct { Lang string "json:\"lang\""; Value string "json:\"value\"" }{Lang:"es", Value:"<informational text>"}} into binary format for text (OID 25): cannot find encode plan (SQLSTATE 57014)
Using:
entries = append(entries, []any{dailycves.Cve.ID, dailycves.Cve.SourceIdentifier, dailycves.Cve.Published, dailycves.Cve.LastModified, dailycves.Cve.Descriptions[0].Value[0]})
To try to copy only the first Value in the Descriptions struct the error is:
Error copying into dailycves table: ERROR: COPY from stdin failed: unable to encode 0x41 into binary format for text (OID 25): cannot find encode plan (SQLSTATE 57014)
I also tried using AfterConnect instead of BeforeAcquire per what a user is here is doing but it fails with:
Error when trying to determine if table in database ERROR: type "cve" does not exist (SQLSTATE 42704)
exit status 1
Code is below:
package main
import (
"context"
"encoding/json"
"fmt"
"io"
"log"
"net/http"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgxpool"
)
type Cve struct {
Cve []CveInfo `json:"vulnerabilities"`
}
type CveInfo struct {
Cve struct {
ID string `json:"id"`
SourceIdentifier string `json:"sourceIdentifier"`
Published string `json:"published"`
LastModified string `json:"lastModified"`
Descriptions []struct {
Lang string `json:"lang"`
Value string `json:"value"`
} `json:"descriptions"`
Metrics struct {
CvssMetricV40 []struct {
CvssData struct {
BaseScore float64 `json:"baseScore"`
} `json:"cvssData"`
} `json:"cvssMetricV40"` // json:"cvssMetricV31" json:"cvssMetricV2"`
} `json:"metrics"`
References []struct {
URL string `json:"url"`
Source string `json:"source"`
} `json:"references"`
} `json:"cve"`
}
func main() {
poolConfig, err := pgxpool.ParseConfig("postgresql://neondb_owner:<password>@<database endpoint>/getdailycves?sslmode=require")
if err != nil {
log.Fatal("Error parsing pgx config: ", err)
}
poolConfig.BeforeAcquire = func(ctx context.Context, conn *pgx.Conn) bool {
RegisterDataTypes(ctx, conn)
return true
}
dbPool, err := pgxpool.NewWithConfig(context.Background(), poolConfig)
if err != nil {
log.Fatal("Error connecting to database: ", err)
}
defer dbPool.Close()
createOrUseCVETable(dbPool)
insertCVEs(dbPool)
}
func RegisterDataTypes(ctx context.Context, conn *pgx.Conn) error {
dataTypeNames := []string{
"CveInfo",
"_CveInfo",
"Cve",
"_Cve",
}
for _, typeName := range dataTypeNames {
dataType, err := conn.LoadType(ctx, typeName)
if err != nil {
return err
}
conn.TypeMap().RegisterType(dataType)
}
return nil
}
func getDailyCVEs(url string) (Cve, error) {
cves := Cve{}
req, err := http.NewRequest(http.MethodGet, url, nil)
if err != nil {
return cves, err
}
res, err := http.DefaultClient.Do(req)
if err != nil {
return cves, err
}
resBody, err := io.ReadAll(res.Body)
if err != nil {
return cves, err
}
err = json.Unmarshal(resBody, &cves)
if err != nil {
return cves, err
}
return cves, nil
}
func createOrUseCVETable(dbPool *pgxpool.Pool) {
var tableExists bool
if err := dbPool.QueryRow(context.Background(), "SELECT EXISTS (SELECT FROM pg_tables WHERE tablename = 'dailycves')").Scan(&tableExists); err != nil {
log.Fatal("Error when trying to determine if table in database ", err)
}
if tableExists == false {
query := `CREATE TABLE IF NOT EXISTS dailycves (
id TEXT,
source TEXT,
published TEXT,
lastmodified TEXT,
description TEXT
)`
fmt.Println("Table not in database; creating table")
_, err := dbPool.Exec(context.Background(), query)
if err != nil {
log.Fatal("Error creating table", err)
}
}
}
func insertCVEs(dbPool *pgxpool.Pool) {
checkRows := dbPool.QueryRow(context.Background(), "SELECT * FROM dailycves")
var expected uint32
checkRows.Scan(&expected)
if expected == 0 {
fmt.Println("\"dailycves\" table is empty; adding CVEs")
url := "https://services.nvd.nist.gov/rest/json/cves/2.0/?pubStartDate=2025-06-24T00:00:00.000&pubEndDate=2025-06-24T23:59:59.000"
cves, err := getDailyCVEs(url)
if err != nil {
log.Fatal("Could not get CVEs", err)
}
entries := [][]any{}
columns := []string{"id", "source", "published", "lastmodified", "description"}
tableName := "dailycves"
for _, dailycves := range cves.Cve {
entries = append(entries, []any{dailycves.Cve.ID, dailycves.Cve.SourceIdentifier, dailycves.Cve.Published, dailycves.Cve.LastModified, dailycves.Cve.Descriptions})
}
_, err = dbPool.CopyFrom(
context.Background(),
pgx.Identifier{tableName},
columns,
pgx.CopyFromRows(entries),
)
if err != nil {
fmt.Printf("Error copying into %s table: %s", tableName, err)
}
}
}