CODEWITHSUNDEEP
postgresqlgojsonbcockroachdb
cpuNram
cpuNram

Reputation: 205

How to update postgres JSONB with variable params in golang?

I have a table in cockroachdb/postgres as below:

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+----------------+--------------------+-----------+-----------+
  id         | STRING    |    false    | NULL           |                       | {primary} |   false    
  student    | JSONB     |    true     | NULL           |                       | {}        |   false    
(2 rows)


     id    |                                     student                                      
+--------------------+----------------------------------------------+
  1        | {"name": "Albert"}                                                                    
  2        | {"name": "Bob", "state": "CA"}

I am trying to update student with variable number of params - for example, some times update age, some times update age and country etc. How do we do this in golang?

Here is what I have so far, but it does not work.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq"
)

var (
    DB        = "testdb"
    DBUSER    = "testuser"
    TESTTABLE = "ttab"
    CONNSTR   = "dbname=" + DB + " user=" + DBUSER + " host=localhost port=26257 sslmode=disable"
    DBDRIVER  = "postgres"
)
var (
    NAME    = "name"
    AGE     = "age"
    STATE   = "state"
    COUNTRY = "country"
)

func update(si map[string]string, id string) error {

    silen := len(si)
    if silen == 0 {
        return fmt.Errorf("si cannot be empty")
    }

    keys := []string{}
    values := []string{}
    for k, v := range si {
        keys = append(keys, k)
        values = append(values, v)
    }

    db, err := sql.Open(DBDRIVER, CONNSTR)
    if err != nil {
        return err
    }

    defer db.Close()

    sqlCmd := "UPDATE " + TESTTABLE + " SET student = student || jsonb_object($1, $2) WHERE id = $3"
    _, err := db.Exec(sqlCmd, keys, values, id)
    return err
}

func main() {

    s := make(map[string]string)
    s[AGE] = "22"
    s[COUNTRY] = "USA"
    if err := updateFast3DB(s3, "1"); err != nil {
        fmt.Printf("err: %v\n", err)
    }
}

Error:

[root@bin]# ./updatedb update error: sql: converting argument $1 type: unsupported type []string, a slice of string [root@bin]#

Upvotes: 1

Views: 752

Answers (1)

Jordan Lewis
Jordan Lewis

Reputation: 17938

For anyone coming to this later, make sure to read libpq's documentation on passing array values to the driver.

To pass an array into the libpq driver, you must wrap the slice in a pq.Array first. In the example above, the code should look like this:

    _, err := db.Exec(sqlCmd, pq.Array(keys), pq.Array(values), id)

Upvotes: 1

Related Questions