How would convert numeric(8,2) to Golang?

[nkev]

I'm relatively new to Golang and need to use a currency column (numeric(8,2)) in my PostGres 9.4 database. What is common pgx practice for this?

Also, I want to later use the new Postgres jsonb datatype (converted to a string in Go is ok). I would appreciate guidance with that too.

[jackc]

Unfortunately, there is no decimal type in the Go standard library so numeric columns will be returned as text. It is relatively simple to create custom type mappings (https://github.com/jackc/pgx/blob/master/example_custom_type_test.go) once you've chosen a type to represent it in Go.

For the JSONB type it should be returned as a string by default.

[nkev]

Thanks. I think that custom type mapping code is a bit out my reach as a newbie Gopher :)

I just did a conversion to float64 and it worked fine (I got my $27.95 Postgres decimal value):

product.Price, err = strconv.ParseFloat(sPrice, 64)

Are there any issues with this? Is this the common practice for working with Postgres datatypes not supported in Go?

[jackc]

Well, it's not a problem specific to Go, but you probably don't want to use floats to represent money.

http://spin.atomicobject.com/2014/08/14/currency-rounding-errors/

It's unfortunate that Go doesn't have a decimal type in its standard library. I haven't had to deal with money in my Go applications yet, so I don't have a good suggestion for a 3rd party decimal library, but there may be one. Another option is to convert to integers and there money as the number of cents.

[nkev]

Thanks for the tip. I may as well use an int64 in both Postgres and the Go model to store and calculate the product price in cents and only add the dot when displaying the price in the browser. There may even be a small performance advantage for using integers...

[jackc]

For what it's worth, in 9f9a977 I added compatibility with the standard database/sql.Scanner and database/sql/driver.Valuer interfaces for custom types. This means you can use https://github.com/shopspring/decimal as a type that maps to PostgreSQL numeric. See https://github.com/jackc/pgx/blob/master/query_test.go#L913 for an example.

[jayd3e]

Thanks for adding this! It's going to majorly help me out on this latest project(financial-related).