duaabhuv188411
duaabhuv188411
2018-07-02 07:01
浏览 390

如何一次性将多行插入Postgres SQL

  1. Is it possible to insert multiple rows into Postgres database at once? Could someone please suggest if there is a way to insert a slice of slices into database. I have created a slice for each row and created a another slice(multiple rows) by appending all the row slices to it. how do I insert the slice(multiple rows) into db?

  2. When I create a row slice, I'm using row := []interface{}{} . Because I have fields which are strings and int in each row. Looks like I get an error when I'm inserting data and the error is unsupported type []interface {}, a slice of interface

Implementation:

rowdata := []interface{}{}
row := []interface{}{data.ScenarioUUID, data.Puid, data.Description, data.Status, data.CreatedBy, data.CreatedAt, data.UpdatedBy, data.UpdatedAt, data.ScopeStartsAt, data.ScopeEndsAt, Metric, MetricName, Channel, date, timeRangeValue}
rowdata = append(rowdata, row)

qry2 := `INSERT INTO sample (scenarioUuid,
            puId,
            description,
            status,
            createdBy,
            createdAt,
            updatedBy,
            updatedAt,
            scopeStartsAt,
            scopeEndsAt,
            metric,
            metric_name,
            channel,
            time,
            value) VALUES ($1, $2, $3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)`

if _, err := db.Exec(qry2, rowdata); err != nil {
    panic(err)

图片转代码服务由CSDN问答提供 功能建议

  1. 是否可以一次在Postgres数据库中插入多行? 有人可以建议是否有一种方法可以将切片插入数据库。 我为每一行创建了一个切片,并通过将所有行切片附加到该切片来创建了另一个切片(多行)。 如何将切片(多行)插入db?

  2. 创建行切片时,我使用的是 row:= [] interface { } {} 。 因为我在每一行中都有字符串和整数字段。 好像我在插入数据时遇到错误,错误是不受支持的类型[] interface {},是接口的一部分

    实现:

      rowdata:= [] interface {} {} 
    row:= [] interface {} {data.ScenarioUUID,data.Puid  ,data.Description,data.Status,data.CreatedBy,data.CreatedAt,data.UpdatedBy,data.UpdatedAt,data.ScopeStartsAt,data.ScopeEndsAt,Metric,MetricName,Channel,date,timeRangeValue} 
    rowdata = append(rowdata, 行)
     
    qry2:=`INSERT INTO sample(scenarioUuid,
     puId,
     description,
     status,
     createdBy,
     createdAt,
     UpdatedBy,
     UpdatedAt,
     scopeStartsAt,
     scopeEndsAt  ,
    指标,
    指标名称,
    频道,
    时间,
    值)VALUES($ 1,$ 2,$ 3,$ 4,$ 5,$ 6,$ 7,$ 8,$ 9,$ 10,$ 11,$ 12,$ 13,$ 14  ,$ 15)`
     
    if _,err:= db.Exec(qry2,rowdata);  err!= nil {
     panic(err)
       
     
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duandan1995
    duandan1995 2018-07-02 08:51
    已采纳

    You could do something like this:

    samples := // the slice of samples you want to insert
    
    query := `insert into samples (<the list of columns>) values `
    
    values := []interface{}{}
    for i, s := range samples {
        values = append(values, s.<field1>, s.<field2>, < ... >)
    
        numFields := 15 // the number of fields you are inserting
        n := i * numFields
    
        query += `(`
        for j := 0; j < numFields; j++ {
            query += `$`+strconv.Itoa(n+j+1) + `,`
        }
        query = query[:len(query)-1] + `),`
    }
    query = query[:len(query)-1] // remove the trailing comma
    
    db.Exec(query, values...)
    

    https://play.golang.org/p/YqNJKybpwWB

    点赞 评论

相关推荐