Can't encode float64 into oid 1700

[isadon]

Hi, I'm using the library with sqlx and am getting this error for a struct like the following:

type PostPerson struct {
    Username    *string     `json:"username" binding:"exists"`
    Photo1URL  pgx.NullString `json:"photo1Url"`
    Latitude    *float64    `json:"latitude" binding:"exists"`
    Longitude   *float64    `json:"longitude" binding:"exists"`
}

and keep getting a : Cannot encode float64 into oid 1700 - float64 must implement Encoder or be converted to a string

In my postgres database:
Latitude is numeric(8,6),
Longitude numeric(9,6),

If i change the latitude or longitude to be pgx.NullFloat64 then I get
json: cannot unmarshal number into Go value of type pgx.NullFloat64

[isadon]

Delving a bit deep in the code it seems the issue is that numeric() is not supported.. I switched out numeric for float in the sql file and then it proceeded to work. Interestingly enough, when using sqlx with lib/pq this was not an issue.

[jackc]

The reason pgx does not natively decode and encode numeric is Go does not have a standard decimal type. Any mapping to or from a Go float is potentially losing data.

The encoding/decoding logic is pretty trivial, probably less than 20 lines of code -- the problem is what Go type to map PostgreSQL numeric to.

[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.

[isadon]

Nice.. Thanks

[reusee]

What about math/big.Rat? It can represent any numeric type without losing precision.

[jackc]

That's true. But https://github.com/shopspring/decimal gives some reason why you might not want to use math/bin.Rat for money (the traditional use case for decimal / numeric).

Though, I suppose it still could be reasonable to have math/bin.Rat as the default mapping.

[aj0strow]

@jackc is there a way to override this behavior? I'm already mapping floats to numeric using gocraft/dbr and want to gradually transition to pgx.

[aj0strow]

The alternative is to convert to and from a new struct, copying every field except converting float to decimal. It works, but it sucks.