SELECT query on unique number field returns no rows 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 &lt;unnamed&gt;: 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 &lt;unnamed&gt;: 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?
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"