英文:
Updating Jsonb column in golang
问题
我正在尝试更新特定行的Jsonb列值。
我运行了以下查询：
UPDATE instruction.file SET "details" = (jsonb_set("details",'{\"UploadBy\"}','\"test@test.com\"'::jsonb, true)) WHERE id=820;
这在pgAdmin3中运行正常。
当我尝试在我的Go代码中执行相同的操作时，我遇到了错误："pq: invalid input syntax for type json"。
我的Go代码如下：
func main() {
uname := "test@test.com"
err := Init() //Db init
if err != nil {
fmt.Println("Error", err)
return
}
result, err1 := Db.Exec("UPDATE instruction.file SET \"details\" = (jsonb_set(\"details\",'{\"UploadBy\"}', $1::jsonb, true)) WHERE id=$2", uname, "820")
if err1 != nil {
fmt.Println("Error", err1)
return
}
n, err1 := result.RowsAffected()
if err1 != nil {
fmt.Println("Error", err1)
return
}
if n != 1 {
err1 = errors.New("Unable to update instruction.file")
fmt.Println("Error", err1)
return
}
fmt.Println("Success")
return
}
英文:
I am trying to update a Jsonb column value for a particular row.
I ran the query
UPDATE instruction.file SET "details" = (jsonb_set("details",'{"UploadBy"}','"test@test.com"'::jsonb, true)) WHERE id=820;
this works fine in the pgAdmin3.
When I tried to do the same in my Go code. Iam getting the error:"pq: invalid input syntax for type json"
My Go code:
func main() {
uname := "test@test.com"
err := Init() //Db init
if err != nil {
fmt.Println("Error", err)
return
}
result, err1 := Db.Exec("UPDATE instruction.file SET \"details\" = (jsonb_set(\"details\",'{\"UploadBy\"}',$1::jsonb, true)) WHERE id=$2", uname, "820")
if err1 != nil {
fmt.Println("Error", err1)
return
}
n, err1 := result.RowsAffected()
if err1 != nil {
fmt.Println("Error", err1)
return
}
if n != 1 {
err1 = errors.New("Unable to update instruction.file")
fmt.Println("Error", err1)
return
}
fmt.Println("Success")
return
}
答案1
得分: 7
使用to_jsonb函数：
Db.Exec(`
UPDATE instruction.file
SET details = jsonb_set("details", '{"UploadBy"}', to_jsonb($1::text), true)
WHERE id = $2
`, uname, "820"
)
英文:
Use to_jsonb:
Db.Exec(`
UPDATE instruction.file
SET details = jsonb_set("details", '{"UploadBy"}', to_jsonb($1::text), true)
WHERE id = $2
`, uname, "820"
)
答案2
得分: 0
我成功解决了错误：
当创建时，"uname" 的类型是字符串，像这样：
uname := "test@test.com"
它应该是一个 JSON 变量。必须像这样创建：
uname := `"test@test.com"`
我不知道这是否是正确的答案，但它有效。
英文:
I managed to remove the error:
"uname" was of the type string, when created like:
uname := "test@test.com"
It should be a json variable. Must be created like:
uname := `"test@test.com"`
I don't know whether it is the right answer. But it works.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论