使用pgx.copyfrom插入Postgres数据库的批量插入CSV数据

发布于 2025-02-05 07:29:04 字数 1060 浏览 3 评论 0原文

我再次尝试将大量CSV数据推入Postgres数据库。

在过去好像我应该能够使它变得更好。

到目前为止，我已经将表的列标题放到了一片字符串中，将CSV数据放入了另一片字符串中，但我无法算出语法将其推入数据库。

到目前为止，我拥有的代码是

// 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))    
      }
   }

}

pgx.copyfromrows Expect [] []接口{}不是[]字符串。

语法应该是什么？我在吠叫错误的树吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

゛清羽墨安 2025-02-12 07:29:05

我建议阅读您的CSV并为您阅读的每个记录创建[] interface {}，将[] interface {}附加到行集合（[[ ] []接口{}），然后将行传递到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),
)

我无法模拟整个程序，但这是将CSV转换为[] [] [] interface {}， https://go.dev/play/p/efbifn2fjmi 。

并在文档中签入， https://pkg.go.do.dev/github/github .com/jackc/pgx/v4 。

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.

回复收藏 0 原文

~没有更多了~