pg batch insert

db.go

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