search

lib / pq

Pure Go Postgres driver for database/sql
https://pkg.go.dev/github.com/lib/pq
MIT License
8.98k stars 909 forks source link

Can this library support bulk update #1002

Open laekettavong opened 3 years ago

laekettavong commented 3 years ago

I'm trying to figure out what is the most performant approach in golang to do a bulk/batch table update (i.e. 150,000 plus records). Can this package support that? If so, some code example to do that would be appreciated!

gtramontina commented 3 years ago

I guess you can achieve what you want by relying on UNNEST. I tried the traditional way and quickly ran out of $ tokens (there is a limitation, but I can't remember from the top of my head). So I ended up using something like the following:

tx.Exec(
  `UPDATE my_table SET field = input.new_field FROM (SELECT UNNEST ($1::bigint[]) AS new_field) AS input`,
  pq.Array(new_fields),
)