What is a Golang prepared statement example for an update statement in PostgreSQL?

A prepared statement in Golang, used with PostgreSQL, allows you to precompile an SQL query. This can significantly improve performance, especially when the same query is executed multiple times with different parameters. Here's an example of how to use a prepared statement for an UPDATE statement in PostgreSQL with Golang:

First, ensure you have the necessary Go packages installed:

go get github.com/lib/pq
go get database/sql

Here's a complete example:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq" // PostgreSQL driver
    "log"
)

func main() {
    connStr := "user=postgres dbname=your_database password=your_password sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Test the connection
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    // Prepare the update statement
    stmt, err := db.Prepare("UPDATE users SET email = $1 WHERE id = $2")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    // Execute the prepared statement with different parameters
    email := "newemail@example.com"
    id := 1
    res, err := stmt.Exec(email, id)
    if err != nil {
        log.Fatal(err)
    }

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("Updated %d rows\n", rowsAffected)
}

Explanation:

1. Import Packages: Import database/sql for database operations, github.com/lib/pq as the PostgreSQL driver, fmt for printing, and log for error handling.

2. Database Connection:

- The connection string connStr specifies the credentials to connect to the PostgreSQL database. Replace your_database, your_password with your actual database name and password.

- sql.Open opens a database connection using the PostgreSQL driver. The sslmode=disable part is important for local development without SSL.

- db.Ping verifies that the connection to the database is working.

3. Prepare Statement:

- db.Prepare creates a prepared statement. The SQL query uses placeholders $1, $2, etc., for parameters.

- Always close the prepared statement after use using defer stmt.Close().

4. Execute Statement:

- stmt.Exec executes the prepared statement with the given parameters. The parameters are passed in the order corresponding to the placeholders.

- The result res contains information about the execution, such as the number of rows affected.

5. Rows Affected:

- res.RowsAffected returns the number of rows affected by the UPDATE statement.

6. Error Handling:

- Error handling is crucial. Always check for errors after each operation.

This example demonstrates the basic structure of using a prepared statement with Golang and PostgreSQL for an UPDATE operation. Remember to handle errors appropriately in a production environment.

Learn more about this