How to insert multiple rows into postgres SQL in a go

Question

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?
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)

Make sure to "unpack" the rowdata slice (using ...) when you are passing it to Exec. i.e. _, err := db.Exec(qry2, rowdata...). — mkopriva
– mkopriva, Commented Jul 2, 2018 at 8:55

mkopriva · Accepted Answer · 2018-07-02 08:51:40Z

14

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

answered Jul 2, 2018 at 8:51

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alexys Over a year ago

Very useful, thankyou. I have a improve to your code using buffer: play.golang.org/p/3z0JQ-Leaix

Matteo Over a year ago

@Alexys why did you use buffers? Thanks

Alexys Over a year ago

@Madeo I used buffers b/c when you use strings is slower. Buffers are optimal to this kind of process.

Collectives™ on Stack Overflow

How to insert multiple rows into postgres SQL in a go

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related