How to Perform Multi-Row Bulk Updates in PostgreSQL with Go?

Multi-Row Bulk Updates in PostgreSQL with Go

When working with large datasets, it's often necessary to update multiple rows in a database table in a single operation. This can optimize performance and reduce code complexity. In PostgreSQL, bulk updates can be achieved using various approaches.

One approach involves using a derived table to construct a set of values to update. By utilizing the FROM clause, you can specify the values for each row that needs to be updated. For example:

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;

The derived table v contains the pairs of (id, column_a, column_b) representing the updates for each row. The UPDATE statement then uses the WHERE clause to match the id values in v with those in the target table t.

In Go, you can execute this query using a prepared statement:

const updateBulkSQL = `
UPDATE t
SET column_a = v.column_a,
    column_b = v.column_b
FROM (VALUES (, , ),
                 (, , )
         ) v(id, column_a, column_b)
WHERE v.id = t.id;
`

func main() {
    db, err := sql.Open("postgres", "")
    if err != nil {
        // Handle error
    }

    stmt, err := db.Prepare(updateBulkSQL)
    if err != nil {
        // Handle error
    }

    _, err = stmt.Exec(1, "FINISH", 1234, 2, "UNFINISH", 3124)
    if err != nil {
        // Handle error
    }
}

This approach provides a concise and efficient way to perform bulk updates in PostgreSQL, allowing you to update multiple rows in a single query without having to execute separate queries for each row.

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