Golang sql PostgreSQL修改範例

Go程式修改一筆資料PostgreSQL資料庫的資料範例如下。

範例環境：

• Go 1.17
• PostgreSQL 14

PostgreSQL的postgres資料庫的employee資料表如下。

CREATE TABLE IF NOT EXISTS employee (
   id bigserial PRIMARY KEY,
   name varchar(60) UNIQUE NOT NULL,
   age integer,
   created_at timestamp NOT NULL
);

PostgreSQL的postgres資料庫的employee資料表有一筆資料如下。

 id | name | age |         created_at
----+------+-----+----------------------------
  1 | john |  33 | 2022-02-15 21:33:43.578068

在專案根目錄執行go get github.com/lib/pq下載driver。

~/../go-demo$ go get github.com/lib/pq

下面程式連線到PostgresSQL資料庫並修改employee資料表的資料。RETURNING用來返回UPDATE後異動資料的id序號。

UpdateEmployee()中調用sql.Tx.Exec()執行修改SQL並帶入參數。

UpdateEmployeeReturnID()中調用sql.Tx.QueryRow()執行修改SQL並帶入參數，接著調用Row.Scan()讀取id欄位值返回。

sql.DB.Begin()、sql.Tx.Rollback()及sql.Tx.Commit()為交易/事務管理範圍。

main.go

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/lib/pq"
)

type Employee struct {
    ID        int64
    Name      string
    Age       int
    CreatedAt time.Time
}

const (
    HOST     = "localhost"
    PORT     = "5432"
    DATABASE = "postgres"
    USER     = "admin"
    PASSWORD = "12345"
    SSL      = "disable"
)

func connect() *sql.DB {
    driver := "postgres"
    dsn := fmt.Sprintf(
        "host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
        HOST, PORT, USER, PASSWORD, DATABASE, SSL)

    db, err := sql.Open(driver, dsn)
    if err != nil {
        panic("open database error")
    }
    return db
}

func main() {
    db := connect()

    emp := Employee{
        ID:   1,
        Name: "john",
        Age:  28,
    }

    err := UpdateEmployee(db, &emp)
    if err != nil {
        panic(err)
    }

    emp.Age = 33
    id, err := UpdateEmployeeReturnID(db, &emp)
    if err != nil {
        panic(err)
    }
    fmt.Println(id)
}

func UpdateEmployee(db *sql.DB, emp *Employee) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    query := `UPDATE employee
            SET 
                name = $1, 
                age = $2
            WHERE id = $3`

    _, err = tx.Exec(query, &emp.Name, &emp.Age, &emp.ID)
    if err != nil {
        return err
    }

    if err = tx.Commit(); err != nil {
        return err
    }

    return nil
}

func UpdateEmployeeReturnID(db *sql.DB, emp *Employee) (int64, error) {
    tx, err := db.Begin()
    if err != nil {
        return 0, err
    }
    defer tx.Rollback()

    query := `UPDATE employee
            SET 
                name = $1, 
                age = $2
            WHERE id = $3
            RETURNING id`

    id := int64(0)
    err = tx.QueryRow(query, &emp.Name, &emp.Age, &emp.ID).Scan(&id)
    if err != nil {
        return 0, err
    }

    if err = tx.Commit(); err != nil {
        return 0, err
    }

    return id, nil
}

github。

• Returning Data from Modified Rows
• Golang sql PostgreSQL查詢範例
• Golang sql PostgreSQL新增範例