Skip to content

Instantly share code, notes, and snippets.

@tbruyelle
Last active October 23, 2020 08:23
Show Gist options
  • Save tbruyelle/e48f8540440ed73804e9c794107c5f48 to your computer and use it in GitHub Desktop.
Save tbruyelle/e48f8540440ed73804e9c794107c5f48 to your computer and use it in GitHub Desktop.
pg batch insert
// usage :
batchInsert(ctx, db,
`INSERT INTO table (...) VALUES`, // the query without the row values
`($%[1]d, $%[2]d, ...)`, // a row value with parametized placeholders
16, // the number of values in a single row
values, // all the values (which can be larger than 65535)
)
// batchInsert aggregates the maximum number of elements to insert in a single query.
func batchInsert(ctx context.Context, db *sql.DB, sql, placeholder string, nbValues int, values []interface{}) error {
if len(values) == 0 {
return nil
}
// pg allows only 65535 placeholders maximum
const max = 65535
var (
placeholders []string
subvalues []interface{}
)
insert := func() error {
stmt := fmt.Sprintf("%s %s", sql, strings.Join(placeholders, ","))
_, err := db.ExecContext(ctx, stmt, subvalues...)
return errors.WithStack(err)
}
for i := 0; i < len(values)/nbValues; i++ {
args := make([]interface{}, nbValues)
for j := 0; j < nbValues; j++ {
args[j] = len(placeholders)*nbValues + j + 1
subvalues = append(subvalues, values[i*nbValues+j])
}
placeholders = append(placeholders, fmt.Sprintf(placeholder, args...))
if len(subvalues)+nbValues > max {
// time to flush the data because we reach the maximum values allowed by pg
err := insert()
if err != nil {
return err
}
placeholders = nil
subvalues = nil
}
}
if len(placeholders) > 0 {
err := insert()
if err != nil {
return err
}
}
return nil
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment