Inserting empty string or null into postgres as null using jackc/pgx

Question

I'm using an external json API that's inconsistent in the way it handles missing values. Sometimes json values show up as empty strings and other times as null. For example...

Case1: datedec and curr are both empty strings.

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

Case2: datedec is null. curr is populated.

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

Here is the struct I'm using to represent a dividend:

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"`
}

The problem I'm having is how to insert either an empty string or null, into the database as NULL. I know I could use an omitempty json tag, but then how would I write a function to handle values I don't know will be missing? For example, Here is my current function to insert a dividend into postgresql using the jackc/pgx package:

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
}

If a value (e.g. datedec or curr) is missing, then this function will error. From this post Golang Insert NULL into sql instead of empty string I saw how to solve Case1. But is there a more general way to handle both cases (null or empty string)?

I've been looking through the database/sql & jackc/pgx documentation but I have yet to find anything. I think the sql.NullString has potential but I'm not sure how I should be doing it.

Any suggestions will be appreciated. Thanks!

Answer 1

There are a number of ways you can represent NULL when writing to the database. sql.NullString is an option as is using a pointer (nil = null); the choice really comes down to what you find easer to understand. Rus Cox commented:

There's no effective difference. We thought people might want to use NullString because it is so common and perhaps expresses the intent more clearly than *string. But either will work.

I suspect that using pointers will be the simplest approach in your situation. For example the following will probably meet your needs:

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
    }
}

Try it in the playground.

You can use the Dividend struct in the same way as you are now when writing to the database; the SQL driver will write the nil as a NULL.

Answer 2

you can also use pgtypes and get the SQL Driver value from any pgtype using the Value() func:

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
}