Batch insert: pq: got 1137892 parameters but PostgreSQL only supports 65535 parameters

[kprimice]

That is a limitation of postgres. Is there a way for sqlx to get around this?

I can of course do multiple bulk inserts but maybe there is a more elegant solution at the package level.

[dmngu9]

unrelated, but it is great if you can share how you do the bulk insert please?

[chaz8080]

Not OP and this isn't a super complete or tested solution, but I did something like this to make it work...

var insertQuery = `
	INSERT INTO your_table (
		value1,
		value2
	) VALUES (
		:value1,
		:value2
	)
`
type MyStruct struct {
	Value1 string `db:"value1"`
	Value2 string `db:"value2"`
}

func BulkInsert(insertQuery string, myStructs []MyStruct, db *sqlx.DB) {
        tx, err := db.Beginx()

	// The number of placeholders allowed in a query is capped at 2^16, therefore,
	// divide 2^16 by the number of fields in the struct, and that is the max
	// number of bulk inserts possible. Use that number to chunk the inserts.
	v := reflect.ValueOf(myStructs[0])
	maxBulkInsert := ((1 << 16) / v.NumField()) - 1

	// send batch requests
	for i := 0; i < len(myStructs); i += maxBulkInsert {
		batch := myStructs[i:Min(i+maxBulkInsert, len(myStructs))]
		_, err := tx.NamedExec(insertQuery, batch)
		if err != nil {
			e := tx.Rollback()
			if e != nil {
				log.Printf("Couldn't rollback %+v", e)
			}
			log.Printf(err)
		}
	}
	
        err = tx.Commit()
	if err != nil {
		e := tx.Rollback()
		if e != nil {
			log.Printf("Couldn't rollback %+v", err)
		}
		log.Printf("Couldn't commit %+v", err)
	}
}


// Min takes 2 ints and returns the lesser of them.
func Min(a, b int) int {
	if a <= b {
		return a
	}
	return b
}

Note: This is on go 1.12 and using github.com/jmoiron/sqlx v1.2.1-0.20190426154859-38398a30ed85

[tahmed-wish]

We just ran into this issue as well, would be very grateful for a fix!

[yoelsusanto]

I faced this problem sometime ago and I used UNNEST to solve it. The main idea is instead of sending many parameters individually, we send it as an array so it counts only as one parameter.

Example inserting multiple addresses for the same customer id:

db, err := sqlx.Connect("postgres", "user=... dbname=...")

customerID := "example"
addresses := []string{"addr1", "addr2"}

res, err := db.Exec(`
	INSERT INTO
		customer_address (customer_id, address)
	SELECT
		$1, address
	FROM UNNEST($2::text[]) addresses(address)
`, customerID, pq.StringArray(addresses))
if err != nil {
	fmt.Println(err)
}

fmt.Println(res.RowsAffected())

If you need to send multiple array for inserting to multiple column, UNNEST also support multiple array. Reference:

• https://klotzandrew.com/blog/postgres-passing-65535-parameter-limit
• https://stackoverflow.com/questions/27836674/unnest-multiple-arrays-in-parallel

[ducquangkstn]

Hi, in term of performance, comparing between using UNNEST vs using transaction vs divide the array into smaller ones, which one is best fit?