英文:
GORM decimal operations in Raw SQL query in UPDATE
问题
我正在使用GORM postgres SQL编写我的数据库查询，并在GORM中有一个原始的SQL查询，如下所示：
convertedCurr = points.Div(config.BaseFactor).Round(2)
sqlCoins := "UPDATE coins SET coins = coins + ?, points = points + ? WHERE tenant = ? AND user_id = ?"
errUpdateCoins = database.GetDbWriteClient().Raw(sqlCoins, convertedCurr, points, tenant, userId).Scan(&coin).Count(&updatedCount).Error
coins 是 decimal.Decimal 类型，config.BaseFactor 也是如此。问题是，我不能再像整数类型一样对它们进行 + 加法操作了。这会导致错误日志显示 text + unknown is invalid operation。
在这里，我有哪些选项可以使其正常工作？
- 我应该将其转换为浮点数吗？
- 有没有办法修改查询使其正常工作？我看到PostgreSQL原生支持
decimal类型。https://www.postgresql.org/docs/10/datatype-numeric.html
因此，问题归结为在原始SQL查询字符串中编写语法，以使用变量以及先前数据库中的coins值进行更新。
谢谢！
英文:
I am using GORM postgres SQL to write my DB queries and have a Raw SQL query in GORM as below
convertedCurr = points.Div(config.BaseFactor).Round(2)
sqlCoins := "UPDATE coins SET coins = coins + ?, points = points + ? WHERE tenant = ? AND user_id = ?"
errUpdateCoins = database.GetDbWriteClient().Raw(sqlCoins, convertedCurr, points, tenant, userId).Scan(&coin).Count(&updatedCount).Error
coins is of type decimal.Decimal and so is config.BaseFactor. Issue is that I cannot possibly do + plus operation on it anymore just as I can with Integer types. This comes down as text + unknown is invalid operation as an error log.
What are my options here to run make this work?
- Should I convert this to float?
- Any way to modify the query to make it work? I see that postgres does support
decimaltype natively. https://www.postgresql.org/docs/10/datatype-numeric.html
So it is boiling down to writing syntax in that raw SQL query string to use variables along with previous database value for coins to update.
Thank you!
答案1
得分: 1
你可以在SQL中使用一些类型转换来解决问题：
sqlCoins := "UPDATE coins SET coins = coins + ?::numeric, points = points + ?::numeric WHERE tenant = ? AND user_id = ?"
或者
sqlCoins := "UPDATE coins SET coins = coins + cast(? as numeric), points = points + cast(? as numeric) WHERE tenant = ? AND user_id = ?"
"unknown" 伪类型用于标识尚未解析的类型，例如未装饰的字符串文字。
英文:
You should be able to get away with some type casts in the SQL:
sqlCoins := "UPDATE coins SET coins = coins + ?::numeric, points = points + ?::numeric WHERE tenant = ? AND user_id = ?"
or
sqlCoins := "UPDATE coins SET coins = coins + cast(? as numeric), points = points + cast(? as numeric) WHERE tenant = ? AND user_id = ?"
The "unknown" pseudo-type "Identifies a not-yet-resolved type, e.g., of an undecorated string literal".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论