how to batch insert and get results by sqlx in postgres

Question

it's easy to insert one record and get result like this:

    s := "INSERT INTO quiz_answer_details (quiz_answer_id, question_id, type, choices, content) VALUES ($1, $2, $3, $4, $5) RETURNING *"
    d, err := fromQuizAnswerDetail(in)
    if err != nil {
        return nil, err
    }
    var out quizAnswerDetail
    if err := m.core.GetContextOnMaster(ctx, &out, s, d.QuizAnswerID, d.QuestionID, d.Type, d.Choices, d.Content); err != nil {
        return nil, err
    }

but how to do batch insert and get all results? i tried several methods but got nothing.

this is one i think should work but it doesn't

    s := "INSERT INTO quiz_answer_details (quiz_answer_id, question_id, type, choices, content) VALUES ($1, $2, $3, $4, $5) RETURNING *"
    data, err := fromQuizAnswerDetails(ins)
    if err != nil {
        return nil, err
    }
    dbs, _ := m.core.GetAllMasters()
    stmt, err := dbs[0].PreparexContext(ctx, s)
    if err != nil {
        return nil, err
    }
    var out quizAnswerDetails
    for _, d := range data {
        var detail quizAnswerDetail
        if err := stmt.GetContext(ctx, &detail, d.QuizAnswerID, d.QuestionID, d.Type, pq.Array(d.Choices), d.Content); err != nil {
            return nil, err
        }
        out = append(out, detail)
    }
    return out.to()

The error message is something like this:

 quiz-answer_test.go:35: driver: skip fast-path; continue as if unimplemented

thanks in advance

Answer 1

I can't help you specifically with sqlx as I am not familiar with that package, but when using the standard library's database/sql package one can do a batch insert as demonstrated below.

It's quite verbose and can become hard to maintain if the number of different batch queries that need to be written is high, or if a project is in the design stage and the db schema is being changed more frequently than one would like. In those cases the example below could be generalized using reflection, or, as I like to do, a generator could be written to write the code for you.

var queryString = `INSERT INTO "quiz_answer_details" (
    "quiz_answer_id"
    , "question_id"
    , "type"
    , "choices"
    , "content"
) VALUES ` // `

// 5 = the number of columns you want to insert per row
params := make([]interface{}, len(ins)*5)
for i, v := range ins {
    pos := i * 5

    // aggregate all fields into a single slice
    params[pos+0] = v.QuizAnswerID
    params[pos+1] = v.QuestionID
    params[pos+2] = v.Type
    params[pos+3] = pq.Array(v.Choices)
    params[pos+4] = v.Content

    // construct the ($N, $N, ...) lists for the VALUES clause
    queryString += `($` + strconv.Itoa(pos+0) +
        `, $` + strconv.Itoa(pos+1) +
        `, $` + strconv.Itoa(pos+2) +
        `, $` + strconv.Itoa(pos+3) +
        `, $` + strconv.Itoa(pos+4) +
        `),`
}

queryString = queryString[:len(queryString)-1] // drop the last comma
queryString += ` RETURNING *`

rows, err := db.QueryContext(ctx, queryString, params...)
if err != nil {
    return err
}
defer rows.Close()

i := 0
for rows.Next() {
    err := rows.Scan(
        // NOTE: since you're using "RETURNING *" you need to
        // provide fields for every column that will be returned,
        // and you need to make sure that the fields are in the
        // same order in which their corresponding columns are
        // declared in the table.
        //
        // NOTE: postgres, AFAIK, does NOT GUARANTEE that the returned
        // rows will be in the same order in which they were inserted,
        // therefore the contents of the input slice may be in a
        // different order after scanning is done.
        &ins[i].QuizAnswerID,
        &ins[i].QuestionID,
        &ins[i].Type,
        pq.Array(&ins[i].Choices),
        &ins[i].Content,
    )
    if err != nil {
        return err
    }

    i += 1
}
return rows.Err()