This is a cross-post of an issue I field on the GORM Github repo. I am unsure if the issue is with Postgres 11 or with GORM.
See: https://github.com/jinzhu/gorm/issues/2872
I have a table containing an ID as primary key and only one more column (mega_herz) which is a numeric(7,3). The numeric field also has a unique constraint on it. When I do the following query from pgadmin4 or psql I get one row as a response:
SELECT *
FROM "ttnmapper_frequencies"
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3)
ORDER BY "ttnmapper_frequencies"."id" ASC
LIMIT 1
But when I do the same query via GORM it does not return any results, and when I try and do an insert it fails:
(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342)
[2020-02-03 14:52:28] [3.07ms]
SELECT *
FROM "ttnmapper_frequencies"
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3)
ORDER BY "ttnmapper_frequencies"."id" ASC
LIMIT 1
[0 rows affected or returned ]
(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:342)
[2020-02-03 14:52:28] [2.10ms]
INSERT INTO "ttnmapper_frequencies"
("mega_herz")
VALUES
(868.3)
RETURNING "ttnmapper_frequencies"."id"
[0 rows affected or returned ]
(/home/jpmeijers/go/src/ttnmapper-postgres-insert-raw/main.go:345)
[2020-02-03 14:52:28] pq: duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key"
Why would the select query return no result? Should I specify the number with three decimals in the where clause?
Update 2020-02-09:
The log output from Postgres shows the following:
2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper LOG: execute <unnamed>: SELECT * FROM "ttnmapper_frequencies" WHERE ("ttnmapper_frequencies"."mega_herz" = $1) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1
2020-02-09 05:43:45.679 UTC [59458] ttnmapper@ttnmapper DETAIL: parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.688 UTC [59458] ttnmapper@ttnmapper LOG: statement: BEGIN READ WRITE
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper LOG: execute <unnamed>: INSERT INTO "ttnmapper_frequencies" ("mega_herz") VALUES ($1) RETURNING "ttnmapper_frequencies"."id"
2020-02-09 05:43:45.689 UTC [59458] ttnmapper@ttnmapper DETAIL: parameters: $1 = '868.2999877929688'
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper ERROR: duplicate key value violates unique constraint "ttnmapper_frequencies_mega_herz_key"
2020-02-09 05:43:45.704 UTC [59458] ttnmapper@ttnmapper DETAIL: Key (mega_herz)=(868.300) already exists.
The query
SELECT * FROM "ttnmapper_frequencies" WHERE ("ttnmapper_frequencies"."mega_herz" = 868.2999877929688) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1
returns no results.
But however the query
SELECT * FROM "ttnmapper_frequencies" WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1
returns a single row.
I therefore have to assume Postgres does not automatically round parameters for select queries. What would be the best solution for this?
Best Answer
The problem was solved when I changed the MegaHerz field's data type in the golang struct definition from a float64 to a decimal.Decimal.
You do however need to include a third party library for the decimal.Decimal fixed point type.
import "github.com/shopspring/decimal"