主页 / dba / 问题 / 258873
Accepted
JPM
Asked: 2020-02-05 06:43:35 +0800 CST

对唯一编号字段的 SELECT 查询不返回任何行

  • 772

这是我在 GORM Github repo 上提交的一个问题的交叉帖子。我不确定问题出在 Postgres 11 还是 GORM 上。

见：https ://github.com/jinzhu/gorm/issues/2872

我有一个表，其中包含一个 ID 作为主键，并且只有一个列 (mega_herz)，它是一个数字 (7,3)。数字字段也有一个唯一的约束。当我从 pgadmin4 或 psql 执行以下查询时，我得到一行作为响应：

SELECT * 
FROM "ttnmapper_frequencies" 
WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3) 
ORDER BY "ttnmapper_frequencies"."id" ASC 
LIMIT 1

但是当我通过 GORM 执行相同的查询时，它不会返回任何结果，并且当我尝试执行插入时，它会失败：

(/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"

为什么选择查询不返回结果？我应该在 where 子句中指定三位小数的数字吗？

2020 年 2 月 9 日更新：

Postgres 的日志输出显示如下：

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.

查询

SELECT * FROM "ttnmapper_frequencies" WHERE ("ttnmapper_frequencies"."mega_herz" = 868.2999877929688) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

不返回任何结果。

但是，查询

SELECT * FROM "ttnmapper_frequencies" WHERE ("ttnmapper_frequencies"."mega_herz" = 868.3) ORDER BY "ttnmapper_frequencies"."id" ASC LIMIT 1

返回单行。

因此，我不得不假设 Postgres 不会自动舍入选择查询的参数。什么是最好的解决方案？

postgresql postgresql-11

1 个回答

  1. Best Answer

    当我将 golang 结构定义中的 MegaHerz 字段的数据类型从 float64 更改为 decimal.Decimal 时，问题就解决了。

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

    但是，您确实需要为 decimal.Decimal 定点类型包含第三方库。

    import "github.com/shopspring/decimal"

    • 0

相关问题