How to Efficiently Perform Bulk Updates in PostgreSQL using Go?

Bulk Update in PostgreSQL with Go Query Builder

When faced with the need to update multiple rows in a PostgreSQL database, the traditional approach involves executing a separate UPDATE statement for each row. However, for performance reasons, it is often desirable to execute a single UPDATE statement to update multiple rows in one go.

Derived Table Approach

One effective method for bulk updating in PostgreSQL is to utilize a derived table. This technique involves creating a temporary table with the desired updates, then using the data in that table to update the target table.

Consider the following scenario where we aim to update multiple rows in the "table" table with new values for columns "column_a" and "column_b":

import (
    "database/sql"
    _"github.com/jackc/pgx/v4/stdlib" // PostgreSQL driver
)

func main() {
    db, err := sql.Open("pgx", "host=localhost port=5432 dbname=test user=postgres password=mysecretpassword")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Prepare the derived table with updated values
    query := `UPDATE t
SET column_a = v.column_a,
    column_b = v.column_b
FROM (VALUES (1, 'FINISH', 1234),
             (2, 'UNFINISH', 3124)
     ) v(id, column_a, column_b)
WHERE v.id = t.id;`
    _, err = db.Exec(query)
    if err != nil {
        panic(err)
    }

    // Optionally, you can verify the updated rows
    rows, err := db.Query("SELECT * FROM table WHERE id IN (1, 2)")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var columnA, columnB string
        if err := rows.Scan(&id, &columnA, &columnB); err != nil {
            panic(err)
        }
        fmt.Printf("ID: %d, Column A: %s, Column B: %s\n", id, columnA, columnB)
    }
}

This approach offers flexibility in adding or removing rows and columns in the derived table without altering the main query. It also helps prevent accidental errors compared to specifying multiple UPDATE statements explicitly.

The above is the detailed content of How to Efficiently Perform Bulk Updates in PostgreSQL using Go?. For more information, please follow other related articles on the PHP Chinese website!