使用Golang+pgx操作PostgreSQL时自定义类型数据增改失败

你的代码存在两个核心问题：一是INSERT语句仅指定了symbol列，却传入了两个参数；二是pgx默认无法识别DayPriceModel结构体（及切片）与PostgreSQL自定义类型的映射关系，需要手动实现编码逻辑，或改用更简便的JSONB存储方式。

一、pgx 实现方案 #

优先推荐以下两种方式，根据你的PostgreSQL表结构选择：

方式1：使用JSONB存储（最简便） #

如果不需要利用PostgreSQL复合类型的查询能力，直接将History序列化为JSONB存储，无需自定义编码器：

1. 创建/修改表结构

CREATE TABLE equity.securities_price_history (
    symbol TEXT PRIMARY KEY,
    history JSONB NOT NULL
);

2. 修正插入代码

pgx会自动将Go结构体切片编码为JSONB，只需补全SQL语句中的history列：

func insertToDB(data SecuritiesPriceHistoryModel) error {
    DBConnection := config.DBConnection
    _, err := DBConnection.Exec(context.Background(), 
        "INSERT INTO equity.securities_price_history (symbol, history) VALUES ($1, $2)", 
        data.Symbol, data.History)
    return err
}

方式2：映射PostgreSQL自定义复合类型数组 #

如果你已创建对应复合类型（如equity.day_price）和数组类型，需为DayPriceModel实现pgx的编码/解码接口：

1. 确保PostgreSQL类型已创建

CREATE TYPE equity.day_price AS (
    date DATE,
    high FLOAT,
    low FLOAT,
    open FLOAT,
    close FLOAT
);

CREATE TABLE equity.securities_price_history (
    symbol TEXT PRIMARY KEY,
    history equity.day_price[] NOT NULL
);

2. 实现pgx编码/解码接口

import (
    "context"
    "time"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgtype"
)

// 编码DayPriceModel为PostgreSQL复合类型
func (d DayPriceModel) EncodeValue(ctx context.Context, ci *pgtype.ConnInfo, buf []byte) ([]byte, error) {
    dateStr := d.Date.Format("2006-01-02")
    compositeStr := pgtype.EncodeComposite([]interface{}{
        dateStr, d.High, d.Low, d.Open, d.Close,
    }, ci)
    return append(buf, compositeStr...), nil
}

// 解码PostgreSQL复合类型到DayPriceModel（查询时使用）
func (d *DayPriceModel) DecodeValue(ctx context.Context, ci *pgtype.ConnInfo, oid pgtype.OID, data []byte) error {
    var parts []interface{}
    if err := pgtype.DecodeComposite(ci, data, &parts); err != nil {
        return err
    }

    dateStr, ok := parts[0].(string)
    if !ok {
        return pgtype.ErrUnknownType
    }
    var err error
    d.Date, err = time.Parse("2006-01-02", dateStr)
    if err != nil {
        return err
    }

    d.High, _ = parts[1].(float32)
    d.Low, _ = parts[2].(float32)
    d.Open, _ = parts[3].(float32)
    d.Close, _ = parts[4].(float32)

    return nil
}

// DB连接建立后注册自定义类型
func RegisterCustomTypes(conn *pgx.Conn) error {
    ci := conn.ConnInfo()
    dayPriceOID := ci.DataTypeForName("equity.day_price").OID
    dayPriceArrayOID := ci.DataTypeForName("_equity.day_price").OID

    pgtype.RegisterType(&pgtype.Type{
        Name:  "equity.day_price",
        OID:   dayPriceOID,
        Value: func() pgtype.Value { return new(DayPriceModel) },
    })

    pgtype.RegisterType(&pgtype.Type{
        Name:  "_equity.day_price",
        OID:   dayPriceArrayOID,
        Value: func() pgtype.Value { return new([]DayPriceModel) },
    })
    return nil
}

3. 修正插入代码

func insertToDB(data SecuritiesPriceHistoryModel) error {
    DBConnection := config.DBConnection
    _, err := DBConnection.Exec(context.Background(), 
        "INSERT INTO equity.securities_price_history (symbol, history) VALUES ($1, $2)", 
        data.Symbol, data.History)
    return err
}

二、database/sql 实现方案 #

使用lib/pq驱动，同样提供两种方式：

方式1：JSONB存储 #

1. 表结构同pgx方式1

2. 实现Valuer/Scanner接口

import (
    "encoding/json"
    "database/sql/driver"
    "fmt"
)

// 为[]DayPriceModel实现数据库值转换
func (h []DayPriceModel) Value() (driver.Value, error) {
    return json.Marshal(h)
}

func (h *[]DayPriceModel) Scan(value interface{}) error {
    data, ok := value.([]byte)
    if !ok {
        return fmt.Errorf("invalid type for History")
    }
    return json.Unmarshal(data, h)
}

3. 插入代码

import "database/sql"

func insertToDB(data SecuritiesPriceHistoryModel) error {
    db := config.DB // database/sql.DB实例
    _, err := db.Exec("INSERT INTO equity.securities_price_history (symbol, history) VALUES ($1, $2)", 
        data.Symbol, data.History)
    return err
}

方式2：映射复合类型数组 #

1. 表结构同pgx方式2

2. 实现Valuer/Scanner接口

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

// 编码DayPriceModel为复合类型字符串
func (d DayPriceModel) Value() (driver.Value, error) {
    dateStr := d.Date.Format("2006-01-02")
    return fmt.Sprintf("(%s,%f,%f,%f,%f)", dateStr, d.High, d.Low, d.Open, d.Close), nil
}

// 解码复合类型字符串到DayPriceModel
func (d *DayPriceModel) Scan(value interface{}) error {
    data, ok := value.([]byte)
    if !ok {
        return fmt.Errorf("invalid type for DayPriceModel")
    }

    var dateStr string
    var high, low, open, close float32
    _, err := fmt.Sscanf(string(data), "(%s,%f,%f,%f,%f)", &dateStr, &high, &low, &open, &close)
    if err != nil {
        return err
    }

    d.Date, err = time.Parse("2006-01-02", dateStr)
    if err != nil {
        return err
    }
    d.High = high
    d.Low = low
    d.Open = open
    d.Close = close
    return nil
}

3. 使用pq.Array插入数组

import (
    "database/sql"
    "github.com/lib/pq"
)

func insertToDB(data SecuritiesPriceHistoryModel) error {
    db := config.DB
    _, err := db.Exec("INSERT INTO equity.securities_price_history (symbol, history) VALUES ($1, $2)", 
        data.Symbol, pq.Array(data.History))
    return err
}

内容的提问来源于stack exchange，提问作者Krushnal Patel