0

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?

  • Is it possible that GORM doesn't recognize quoted names or that they need some escape character? – McNets Feb 4 at 15:26
  • I'd set log_statement = all in PostgresSQL and see the statement that is actually executed. – Laurenz Albe Feb 4 at 16:16
  • Yes, adding log output shows what is going on. Original post updated. – JPM Feb 9 at 5:52
  • 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() – a_horse_with_no_name Feb 9 at 8:51
0

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"

| improve this answer | |
  • Please accept your answer, so that the question is marked as resolved. – a_horse_with_no_name May 23 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.