使用jackc/pgx将空字符串或null插入PostgreSQL作为null

Question

我正在使用一个外部的JSON API，它在处理丢失值时表现不一致。有时JSON值显示为空字符串，而其他时候则为null。例如...

情况1：datedec和curr都是空字符串。

{
    "symbol": "XYZ",
    "dateex": "2020-09-01",
    "datedec": "",
    "amount": "1.25",
    "curr": "",
    "freq": "annual"
}

案例2: datedec 为空。 curr 已填充。

{
    "symbol": "XYZ",
    "dateex": "2020-09-01",
    "datedec": null,
    "amount": "1.25",
    "curr": "USD",
    "freq": "annual"
}

这是我用来表示股息的结构体：

type Dividend struct {
    symbol   string `json:"symbol"`
    dateex   string `json:"dateex"`
    datedec  string `json:"datedec"`
    amount   string `json:"amount"`
    curr     string `json:"curr"`
    freq     string `json:"freq"`
}

我遇到的问题是如何将空字符串或 null 插入数据库作为 NULL。我知道可以使用 omitempty json 标签，但是如果我不知道值是否缺失，该如何编写处理函数？例如，这是我的当前函数，使用 jackc/pgx 包将分红插入 postgresql：

func InsertDividend(d Dividend) error {
    sql := `INSERT INTO dividends 
    (symbol, dateex, datedec, amount, curr, freq)
    VALUES ($1, $2, $3, $4, $5, $6)`
    conn, err := pgx.Connect(ctx, "DATABASE_URL")
    // handle error 
    defer conn.Close(ctx)
    tx, err := conn.Begin()
    // handle error
    defer tx.Rollback(ctx)
    _, err = tx.Exec(ctx, sql, d.symbol, d.dateex, d.datedec, d.amount, d.curr, d.freq)
    // handle error
    }
    err = tx.Commit(ctx)
    // handle error
    return nil
}

如果某个值（如datedec或curr）缺失，则此函数将报告错误。从这篇文章Golang Insert NULL into sql instead of empty string中，我了解到如何解决Case1的问题。但是是否有一种更通用的方法来处理两种情况（null或空字符串）？

我一直在查阅database/sql和jackc/pgx文档，但还没有找到任何答案。我认为sql.NullString可能有潜力，但我不确定该如何做。

任何建议都将不胜感激。谢谢！

- 7rhvnn

---

在你链接的SO帖子中，有一个答案展示了一个将""转换为sql.NullString的函数。你可以将任何想要转换的变量包装在该函数中：_, err = tx.Exec(ctx, sql, d.symbol, d.dateex, NewNullString(d.datedec), d.amount, NewNullString(d.curr), d.freq)。这个方法不会在你的情况下起作用吗？ - phonaputer

请注意，您的“Dividend”类型将永远无法与json包或任何需要修改其字段的其他包一起使用，因为所有字段都是未公开的。此外，请注意，“omitempty”仅在json 编组/编码期间才有意义，在当前情况下取消编组/解码和数据库持久性中没有任何相关性。 - mkopriva

@7rhvnn Brits提供的另一种选择是在SQL语句中使用NULLIF。例如：INSERT INTO dividends (... datedec, ...) VALUES (... NULLIF($3, ''), ...) - mkopriva

如果您正在使用的Web服务出现故障，请在使用该Web服务时修复您的数据。不要让脏数据进入您的数据库。在Go中，您可以很容易地钩入解封过程并利用类型（如json.RawMessage）和/或实现自定义解封函数（https://golang.org/pkg/encoding/json/#example_RawMessage_unmarshal）。这样，您可以修复值的数据类型，并在此过程之后拥有一个规范的值对象来处理。 - Dynom

@7rhvnn NULLIF 函数返回它的第一个参数值，如果它不匹配第二个参数。如果第一个参数是 NULL 而第二个参数是 ''（空字符串），那么它将返回第一个参数，即 NULL。因此，是的，当 $3 是 NULL 或者 $3 是 ''（空字符串）时，NULLIF($3, '') 都会返回 NULL。 - mkopriva

Answer 1

写入数据库时，有多种方式可以表示NULL。使用sql.NullString或使用指针（nil=null）都是一种选择；选择取决于您觉得哪个更容易理解。Rus Cox在评论中说：“没有实质性的区别。我们认为人们可能想使用NullString，因为它非常常见，也许比*string更清晰地表达了意图。但两者都可以工作。”我猜在您的情况下，使用指针可能是最简单的方法。例如，以下内容可能符合您的需求：

type Dividend struct {
    Symbol  string  `json:"symbol"`
    Dateex  string  `json:"dateex"`
    Datedec *string `json:"datedec"`
    Amount  string  `json:"amount"`
    Curr    *string `json:"curr"`
    Freq    string  `json:"freq"`
}

func unmarshal(in[]byte, div *Dividend) {
    err := json.Unmarshal(in, div)
    if err != nil {
        panic(err)
    }
    // The below is not necessary unless if you want to ensure that blanks
    // and missing values are both written to the database as NULL...
    if div.Datedec != nil && len(*div.Datedec) == 0 {
        div.Datedec = nil
    }
    if div.Curr != nil && len(*div.Curr) == 0 {
        div.Curr = nil
    }
}

在playground中试一试。

当您向数据库写入时，可以像现在使用Dividend结构体，SQL驱动程序会将nil写为NULL。

- Brits

---

使用sql.NullString如何实现这个呢？我还需要编写一个自定义Unmarshal函数或重写func（* NullString）Scan或func（* NullString）Value函数吗？ - 7rhvnn

是的 - 可以参考这个问题的答案 - 由于这是相当常见的情况，因此有像null这样的包可以为您完成此操作。 - Brits

在SQL中，Null和空格是不同的概念，在JSON中，未定义和空白也是不同的。Go标准库提供了处理大多数情况的方法，第三方库（例如null/zero）简化了处理较少见的需求。如果您有一个特定的问题尚未得到解决，最好在新问题中提出。 - Brits

我最终使用了 COALESCE。 - Matteo

字符串指针？真的吗？ - TheRealChx101

Answer 2

您还可以使用pgtypes，并使用Value（）函数从任何pgtype获取SQL驱动程序值：

https://github.com/jackc/pgtype

https://github.com/jackc/pgtype/blob/master/text.go

type Dividend struct {
    symbol   pgtype.Text `json:"symbol"`
    dateex   pgtype.Text `json:"dateex"`
    datedec  pgtype.Text `json:"datedec"`
    amount   pgtype.Text `json:"amount"`
    curr     pgtype.Text `json:"curr"`
    freq     pgtype.Text `json:"freq"`
}

func InsertDividend(d Dividend) error {
    // --> get SQL values from d
    var err error
    symbol, err := d.symbol.Value() // see https://github.com/jackc/pgtype/blob/4db2a33562c6d2d38da9dbe9b8e29f2d4487cc5b/text.go#L174
    if err != nil {
        return err
    }
    dateex, err := d.dateex.Value()
    if err != nil {
        return err
    }
    // ...

    sql := `INSERT INTO dividends 
    (symbol, dateex, datedec, amount, curr, freq)
    VALUES ($1, $2, $3, $4, $5, $6)`
    conn, err := pgx.Connect(ctx, "DATABASE_URL")
    defer conn.Close(ctx)
    tx, err := conn.Begin()
    defer tx.Rollback(ctx)
    // --> exec your query using the SQL values your get earlier
    _, err = tx.Exec(ctx, sql, symbol, dateex, datedec, amount, curr, freq)
    // handle error
    }
    err = tx.Commit(ctx)
    // handle error
    return nil
}

- Big_Boulard