I'm once again trying to push lots of csv data into a postgres database.
In the past I've created a struct to hold the data and unpacked each column into the struct before bumping the lot into the database table, and that is working fine, however, I've just found pgx.CopyFrom* and it would seem as though I should be able to make it work better.
So far I've got the column headings for the table into a slice of strings and the csv data into another slice of strings but I can't work out the syntax to push this into the database.
I've found this post which sort of does what I want but uses a [][]interface{} rather than []strings.
The code I have so far is
// loop over the lines and find the first one with a timestamp
for {
line, err := csvReader.Read()
if err == io.EOF {
break
} else if err != nil {
log.Error("Error reading csv data", "Loading Loop", err)
}
// see if we have a string starting with a timestamp
_, err := time.Parse(timeFormat, line[0])
if err == nil {
// we have a data line
_, err := db.CopyFrom(context.Background(), pgx.Identifier{"emms.scada_crwf.pwr_active"}, col_headings, pgx.CopyFromRows(line))
}
}
}
But pgx.CopyFromRows expects [][]interface{} not []string.
What should the syntax be? Am I barking up the wrong tree?
CodePudding user response:
I recommend reading your CSV and creating a []interface{}
for each record you read, appending the []interface{}
to a collection of rows ([][]interface{}
), then passing rows on to pgx.
var rows [][]interface{}
// read header outside of CSV "body" loop
header, _ := reader.Read()
// inside your CSV reader "body" loop...
row := make([]interface{}, len(record))
// use your logic/gate-keeping from here
row[0] = record[0] // timestamp
// convert the floats
for i := 1; i < len(record); i {
val, _ := strconv.ParseFloat(record[i], 10)
row[i] = val
}
rows = append(rows, row)
...
copyCount, err := conn.CopyFrom(
pgx.Identifier{"floaty-things"},
header,
pgx.CopyFromRows(rows),
)
I can't mock up the entire program, but here's a full demo of converting the CSV to [][]interface{}
, https://go.dev/play/p/efbiFN2FJMi.
And check in with the documentation, https://pkg.go.dev/github.com/jackc/pgx/v4.