How Can I Efficiently Perform Bulk Row Updates in PostgreSQL Using Derived Tables?

Efficient Bulk Row Updates in PostgreSQL

Bulk updating multiple rows in a single query is a common optimization used to improve database performance. PostgreSQL provides powerful mechanisms to facilitate this process.

Derived Table Approach

One effective approach for bulk updates in PostgreSQL is utilizing a derived table. This approach involves creating a temporary table with the desired updates and then using it to modify the target table.

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;

This query creates a derived table v with the specific row updates. The UPDATE statement then uses this table to modify the t table. The WHERE clause ensures that the updates are applied only to the correct rows.

Go Language Example

To execute this query in Go, you can use the following code snippet:

import (
    "context"
    "database/sql"
    "fmt"
)

func main() {
    db, err := sql.Open("postgres", "host=localhost user=postgres password=password dbname=postgres sslmode=disable")
    if err != nil {
        panic(err)
    }
    ctx := context.Background()

    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.ExecContext(ctx, query)
    if err != nil {
        panic(err)
    }

    fmt.Println("Rows updated successfully.")
}

Benefits of Derived Table Approach

• Flexibility: Easily add or remove rows and columns to the update without modifying the primary query.
• Simplicity: The derived table approach is relatively simple to understand and implement.
• Efficiency: It performs well in scenarios where a large number of rows need to be updated.

The above is the detailed content of How Can I Efficiently Perform Bulk Row Updates in PostgreSQL Using Derived Tables?. For more information, please follow other related articles on the PHP Chinese website!