Error: Invalid length for float8

[breakbuidl]

trafficstars

I want to scan a numeric(30,6) field into Float8. But, I get the following error https://github.com/jackc/pgtype/blob/94e10b98b1558e160816e82090e68dd9a7e8b66c/float8.go#L207

I tried using float64 directly and it works fine. But, I wanna handle null values, hence the use of Float8. For now, I am scanning into shopspring-numeric and converting it into float64 using AssignTo()

(Note: It's not a currency value field, so okay to lose precision.)

// Using pgxscan from scany to scan values

type struct Read {
    Field1 pgtype.Float8
}

rs := pgxscan.NewRowScanner(rows)
for rows.Next() {
    read := model.Read{}

    if err := rs.Scan(&read); err != nil {
       ...
    }
}

[breakbuidl]

Similar case with Int8. Trying to scan bigint into Int8 but got the following error https://github.com/jackc/pgtype/blob/94e10b98b1558e160816e82090e68dd9a7e8b66c/int8.go#L206

[jackc]

PostgreSQL numeric type has a different binary format than float8 or bigint. The DecodeBinary method implemented on pgtype.Float8 and pgtype.Int8 expects the incoming data to be a PostgreSQL float8 or bigint respectively.

It's a fundamental design constraint with the pgtype system. Every type is designed to work with only one underlying PostgreSQL type. Because pgtype.Float8 and pgtype.Int8 implement DecodeBinary the query method assumes that they know how to handle the result. Scanning to float64 works because it does not implement any decoding methods so a default pgtype.Numeric is used -- and it knows how to convert itself to a float64 (in the AssignTo method).

Incidentally, I've made a significant rewrite of pgtype in the pgx v5-dev branch that removes this constraint but I would not recommend anything more than experimentation with that branch yet.

As far as what you can do now, you could force the text format for that query by using pgx.QueryResultFormats{pgx.TextFormatCode} as the first argument to your query. The text format of all of those number types is the same. Or you could have PostgreSQL return the correct type by converting it in your SQL like so select mynumeric::float8 from mytable.

[breakbuidl]

Thank for the response, it solved my problem. And. sorry for the delay, just got back to this.

One more thing, is there a reason for not having Marshal/Unmarshal JSON for Float4 and Float8? Because if it did, it would be a lot easier for me. I could just use mynumeric::float8 from mytable and store it in pgtype.Float8

For now, I scan into shopspring.Numeric and use the below function because I don't want numeric to be string in JSON response.

func NumericToFloat(src *shopspring.Numeric) interface{} {

   if src.Status == pgtype.Null {
       return nil
   }

   var temp float64
   src.AssignTo(&temp)
   return temp

[jackc]

One more thing, is there a reason for not having Marshal/Unmarshal JSON for Float4 and Float8? Because if it did, it would be a lot easier for me. I could just use mynumeric::float8 from mytable and store it in pgtype.Float8

Only reason is no one got around to it.

For now, I scan into shopspring.Numeric and use the below function because I don't want numeric to be string in JSON response.

Some JSON parsers will lose information if your data does not neatly fit in to a float64. That is why it can make sense to have numeric be represented as a string in JSON.