英文:
failed to check if row with value exists In Postgres with Golang
问题
我正在尝试使用Golang和Postgres在我的Telegram Bot中创建注册功能。当用户输入"register"时，机器人需要检查用户的UUID是否已经存在于数据库中，如果不存在，则创建带有该UUID的行。
这是我用于检查UUID是否已经存在于数据库中的函数：
func IsUserInDB(uuid int64) (bool, error) {
var exists bool
query := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM users WHERE uuid = %d);", uuid)
err := Db.QueryRow(query).Scan(&exists)
return exists, err
}
这是我用于将用户的UUID添加到数据库中的函数：
func AddUserToDB(column string, row interface{}) error {
query := fmt.Sprintf("INSERT INTO users (%s) VALUES (%v);", column, row)
_, err := Db.Exec(query)
return err
}
以及机器人的逻辑：
func (b *Bot) handleMessages(message *tgbotapi.Message) error {
switch message.Text {
case "register":
exists, err := data.IsUserInDB(message.From.ID)
if err != nil {
return err
}
if !exists {
err := data.AddUserToDB("uuid", message.From.ID)
return err
}
return nil
default:
msg := tgbotapi.NewMessage(message.Chat.ID, "unknown message...")
_, err := b.bot.Send(msg)
return err
}
}
第一次发送"register"时，机器人成功将用户的ID添加到数据库中，但是如果我尝试再次发送"register"，问题就出现了。IsUserInDB()函数返回false，机器人会再次添加一个具有相同UUID的行。所以，我认为问题出在我的IsUserInDb()函数中。
英文:
I'am trying to create registration in my Telegram Bot with Golang and Postgres. When user writes "register", bot has to check if user's uuid already exists in DB, and if not to create row with his uuid.
Here is my function to check if uuid already exists in DB:
func IsUserInDB(uuid int64) (bool, error) {
var exists bool
query := fmt.Sprintf("SELECT EXISTS(SELECT 1 FROM users WHERE uuid = %d);", uuid)
err := Db.QueryRow(query).Scan(&exists)
return exists, err
}
Here is my function for adding user's uuid to DB:
func AddUserToDB(column string, row interface{}) error {
query := fmt.Sprintf("INSERT INTO users (%s) VALUES (%v);", column, row)
_, err := Db.Exec(query)
return err
}
And the logic for bot:
func (b *Bot) handleMessages(message *tgbotapi.Message) error {
switch message.Text {
case "register":
exists, err := data.IsUserInDB(message.From.ID)
if err != nil {
return err
}
if !exists {
err := data.AddUserToDB("uuid", message.From.ID)
return err
}
return nil
default:
msg := tgbotapi.NewMessage(message.Chat.ID, "unknown message...")
_, err := b.bot.Send(msg)
return err
}
}
First time, when I send "register", bot successfully adds user's id to db, but the problem happens if I try to send "register" 1 more time. IsUserInDB() returns me false and bot adds 1 more row with the same uuid. So, I think problem is with my IsUserInDb() function
答案1
得分: 1
为什么不在你的用户表上创建一个唯一索引？
CREATE UNIQUE INDEX unq_uuid ON users (uuid);
这样你就不需要检查，只需尝试插入，如果已存在则会返回错误。
英文:
Why not just a unique index on your users table?
CREATE UNIQUE INDEX unq_uuid ON users (uuid);
Then you don't have to check, you just try to insert and it will return an error if it already exists.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论