How to Perform Bulk Updates in PostgreSQL with Go?-Golang-php.cn

How to Perform Bulk Updates in PostgreSQL with Go?

Susan Sarandon

Release： 2024-12-13 11:32:11

Original

433 people have browsed it

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;

Copy after login

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)
}

Copy after login

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!