CODEWITHSUNDEEP
postgresqlgo
Hardy
Hardy

Reputation: 285

Updating Jsonb column in golang

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"}','"[email protected]"'::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 := "[email protected]"

    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
}

Upvotes: 6

Views: 5106

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

Use to_jsonb:

Db.Exec(`
    UPDATE instruction.file
    SET details = jsonb_set("details", '{"UploadBy"}', to_jsonb($1::text), true)
    WHERE id = $2
    `, uname, "820"
)

Upvotes: 7

Hardy
Hardy

Reputation: 285

I managed to remove the error:

"uname" was of the type string, when created like:

uname := "[email protected]"

It should be a json variable. Must be created like:

uname := `"[email protected]"`

I don't know whether it is the right answer. But it works.

Upvotes: 0

Related Questions