SELECT query on unique number field returns no rows

Question

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?

• McNets: Is it possible that GORM doesn't recognize quoted names or that they need some escape character?
• Laurenz Albe: I'd set log_statement = all in PostgresSQL and see the statement that is actually executed.
• JPM: Yes, adding log output shows what is going on. Original post updated.
• user1822: Well if the column contains the value 868.3 then obviously comparing it to 868.2999877929688 won't return anything. If you want to compare rounded values, you need to use round()

Answer 1

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.

type Frequency struct {
 ID uint
 MegaHerz decimal.Decimal `gorm:"unique;not null;type:numeric(7,3)"`
 Packets []Packet
}

You do however need to include a third party library for the decimal.Decimal fixed point type.

import "github.com/shopspring/decimal"