4

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!

Improve this question
8
  • 1
    In the SO post you linked one answer shows a function to convert from "" to sql.NullString. You can wrap any variables you want to convert in that function: _, err = tx.Exec(ctx, sql, d.symbol, d.dateex, NewNullString(d.datedec), d.amount, NewNullString(d.curr), d.freq). Won't that work in your case? Commented Sep 7, 2020 at 23:56
  • @7rhvnn Note that your Dividend type will never work with the json package, or any other package that needs to modify its fields, because all your fields are unexported. Note also that omitempty is significant only during json marshaling / encoding, it has no relevance whatsoever in your current scenario unmarshaling / decoding and db persistance. Commented Sep 8, 2020 at 4:06
  • 1
    @7rhvnn an alternative to the answer provided by Brits is to use NULLIF in the sql string. e.g. INSERT INTO dividends (... datedec, ...) VALUES (... NULLIF($3, ''), ...) Commented Sep 8, 2020 at 4:13
  • 2
    If the webservice you're consuming is broken, fix your data while consuming that webservice. Don't let dirty data enter your database. In Go you can quite easily hook into the unmarshalling process and utilise types such as json.RawMessage and/or implement a custom unmarshal function (golang.org/pkg/encoding/json/#example_RawMessage_unmarshal). This way you can fix the data types of the values and have a canonical value object to work with after that process. Commented Sep 8, 2020 at 7:01
  • @phonaputer That solves the problem. Prior to posting I wrapped that function around all the values EXCEPT for datedec, so that was an oversight on my part. Commented Sep 8, 2020 at 15:56

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

How could this be implement using sql.NullString? Would I also have to write a custom Unmarshal function or overwrite the func (*NullString) Scan or func (*NullString) Value function?
Yes - see the answers to this question - as this is fairly common there are packages like null that will do this for you.
well that is awful...Not your answer, just this mechanism, is there a way to zero the value? Like empty string?
@Madeo In SQL Null and blanks are different things and in JSON undefined and blanks are different things. The Go standard library provides a way of handling most cases and third party libraries (e.g. null/zero) simplify handling less common requirements. If you have a specific issue that is not already addressed it's probably best to ask it in a new question.
I have ended up using COALESCE
|
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
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.