Pgx: float32 value fails to fetch result

[alleyshairu]

I have been facing this problem where I'm not sure why using float32 fails to fetch the records.

Schema: Postgres version: 12.2

create table sample (
   "id" serial NOT NULL PRIMARY KEY,
   "number" numeric(6,2) NOT NULL,
);
insert into sample (number) values (2222.16);

Go v1.14.1 and pgx: v4.6.0:

works using float64
conn.QueryRow(context.Background(), "SELECT id, number FROM sample WHERE number = $1", 2222.16)

fails: return pgx.ErrNoRows
conn.QueryRow(context.Background(), "SELECT id, number FROM sample WHERE number = $1", float32(2222.16))

But it gets weird, because for some values even float32 works. Like I had two records in database having decimal values as 315.50 and 315.60. I could get the result for 315.50, but not for 315.60 using float32.

I've created this issue, because I believe this to be a bug. Any help would appreciated.
Thanks.

[jackc]

It sounds like a precision issue. There was a recent improvement in numeric / float conversions on github.com/jackc/pgtype. Pull in master and see if that resolves the issue.

Beyond that, comparing floats for equality is fraught with error. If your database has numeric values I suggest using decimal values in Go. In particular, the https://github.com/shopspring/decimal decimal library and https://github.com/jackc/pgtype/tree/master/ext/shopspring-numeric to integrate it seamlessly with pgx.

[alleyshairu]

Makes sense.

Also, I did try the new master version of pgtype, and it still doesn't match for float32.

I'll look in to the mentioned decimal package and thank you for your time.