英文:
Go database/sql Changing Case of Table Name
问题
我正在尝试使用database/sql和github.com/lib/pq Postgres驱动程序查询数据库。我遇到的错误是：
pq: relation "itemprices_itemsale" does not exist
然而，看着我的查询语句：
rows, err := db.Query("SELECT * FROM \"itemPrices_itemsale\" LIMIT 10")
你会注意到表名中有一个大写的 'P'。我从探索中了解到，如果未加引号，Postgres会将名称转换为小写。我已经引用了我的表名，所以我不太确定为什么会发生这种情况。我相当确定这是问题所在，因为我能够从类似的Python程序中使用该表名查询表，并且一切都按预期工作。
更新：
使用@JohnWeldon的建议：
var table = "itemPrices_itemsale"
rows, err := db.Query(fmt.Sprintf("SELECT * FROM %s LIMIT 10", pq.QuoteIdentifier(table)))
英文:
I am trying to query a database using database/sql and the github.com/lib/pq Postgres driver. The error I'm encountering is:
pq: relation "itemprices_itemsale" does not exist
However looking at my query:
rows, err := db.Query("SELECT * FROM \"itemPrices_itemsale\" LIMIT 10")
You'll notice the capital 'P' in the table name. I've learned form poking around that Postgres will fold names in to lowercase if they are not quoted. I have quoted my table name so I'm not quite sure why this is happening. I'm fairly certain this is the issue as I'm able to query the table using that table name from a similar Python program and everything is working as expected.
Update:
Using @JohnWeldon's suggestion:
var table = "itemPrices_itemsale"
rows, err := db.Query(fmt.Sprintf("SELECT * FROM %s LIMIT 10", pq.QuoteIdentifier(table)))
答案1
得分: 1
尝试使用github.com/lib/pq中的QuoteIdentifier函数来引用你的表名：
https://godoc.org/github.com/lib/pq#QuoteIdentifier
英文:
Try using the QuoteIdentifier function in github.com/lib/pq to quote your table name:
答案2
得分: 0
接受的答案是有效的，并且在从不受信任的来源接收表名时可以避免 SQL 注入，但对于一个简单的查询来说有点冗长。
你可以直接使用反引号并手动引用表名：
rows, err := db.Query(`SELECT * FROM "itemPrices_itemsale" LIMIT 10`)
英文:
The accepted answer works and may avoid SQL injections when receiving the table name from an untrusted source, but is a bit verbose for a simple query.
You can just use backsticks and quote the table name manually:
rows, err := db.Query(`SELECT * FROM "itemPrices_itemsale" LIMIT 10`)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论