Golang 查詢PostgreSQL uuid型態欄位

Go查詢PostgreSQL的uuid型態欄位可用string或UUID來接收。

範例環境：

• Go 1.17
• PostgreSQL 14
• github.com/google/uuid
• github.com/lib/pq

uuid資料

例如employee資料表的id欄位型態為uuid且現有2筆資料如下。

postgres=# SELECT * FROM employee;
                  id                  | name | age |         created_at
--------------------------------------+------+-----+----------------------------
 f3f6148e-1ac3-4ae4-b96e-16f937393a10 | john |  33 | 2021-12-14 23:07:39.015527
 080fa120-1d7c-4bf8-a58b-944e1b110205 | mary |  28 | 2021-12-14 23:07:39.017879
(2 rows)

Go查詢uuid

Go查詢employee.id uuid型別欄位的值時可用string或uuid套件的UUID來接收。

package main

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

    "github.com/google/uuid"
    _ "github.com/lib/pq"
)

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

const (
    HOST     = "localhost"
    PORT     = "5432"
    DATABASE = "postgres"
    USER     = "<username>" // 使用者名稱
    PASSWORD = "<password>"
    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 {
        log.Panic("open database error")
    }
    return db
}

func main() {
    db := connect()

    rows, err := db.Query("SELECT id, name, age, created_at FROM employee")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    employees := []Employee{}
    for rows.Next() {
        var e Employee
        err = rows.Scan(&e.ID, &e.Name, &e.Age, &e.CreatedAt)
        if err != nil {
            panic("scan error")
        }
        employees = append(employees, e)
    }

    fmt.Println(employees[0].ID) // f3f6148e-1ac3-4ae4-b96e-16f937393a10
}

參考github。

• Golang sql PostgreSQL查詢範例
• Golang 使用sqlc 轉譯SQL為go程式碼