How to Perform Bulk Updates in PostgreSQL with Go?

Bulk Update Multiple Rows in a Single Query using PostgreSQL

Question:

How can multiple rows be updated simultaneously using a single SQL statement in PostgreSQL and Go?

Answer:

To achieve bulk updates in PostgreSQL using a single query, it is recommended to employ a derived table. The following example demonstrates how to update multiple rows using a derived 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;

Go Implementation:

The following Go code demonstrates executing the bulk update query:

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

    _ "github.com/lib/pq" // PostgreSQL driver
)

func main() {
    // Open a database connection
    db, err := sql.Open("postgres", "user=postgres password=mypassword host=localhost port=5432 database=mydatabase")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Create the derived table
    derivedTableQuery := `
CREATE TEMP TABLE BulkUpdate AS
SELECT 1 AS id, 'FINISH' AS column_a, 1234 AS column_b
UNION ALL
SELECT 2, 'UNFINISH', 3124
`
    if _, err := db.Exec(derivedTableQuery); err != nil {
        panic(err)
    }

    // Execute the bulk update query
    bulkUpdateQuery := `
UPDATE t
SET column_a = v.column_a,
    column_b = v.column_b
FROM BulkUpdate v
WHERE v.id = t.id
`

    // Create a context for the query execution
    ctx := context.Background()

    // Execute the bulk update query
    result, err := db.ExecContext(ctx, bulkUpdateQuery)
    if err != nil {
        panic(err)
    }

    // Retrieve the number of rows affected
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        panic(err)
    }

    // Print the number of rows affected
    fmt.Printf("%d rows updated\n", rowsAffected)
}

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