How to insert a hstore object using lib/pq to postgres

Question

For this table,

# \d table
                            Table "public.table"
  Column   |            Type             | Collation | Nullable |      Default
------------+-----------------------------+-----------+----------+--------------------
id         | uuid                        |           | not null | uuid_generate_v4()
my_field   | hstore                      |           |          |
Indexes:
  "table_pkey" PRIMARY KEY, btree (id)

How do I update the my_field using lib/pq? I tried following,

package "main"

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

func main() {
  postgresConn, _ := sql.Open("postgres", os.Getenv("DB_CONN_URL"))
  id := "024b54f2-a477-4715-984c-896bf0446dcf"
  data := map[string]string{"data": "data"}
  postgresConn.QueryRow("UPDATE table SET my_field = $1 WHERE id = $2", data, id)
}

I'm not sure which other type can be used.

Yeah, I did take a look at it but how do I typecast my string map to it? — mlemboy, Mar 05 '19 at 08:15
You don't, because there's no type casting in Go. You'll have to write a function that creates an `hstore.Hstore` value from your map value. Alternatively you could declare a custom type that suits your needs better and then re-implement the `Scan` and `Value` methods from the `hstore` package. — mkopriva, Mar 05 '19 at 08:27

rnicolas · Answer 1 · 2019-03-08T15:47:38.017

lib/pq has support for hstore, as it stated mkopriva you can find information here. But it may need some clarification or better examples.

First of all, Hstore in this driver is a struct that contains a map:

type Hstore struct {
    Map map[string]sql.NullString
}

So, if you want to use that map, you first need to initialize it:

h := hstore.Hstore{}
h.Map = make(map[string]sql.NullString)

Then, you are able to use it, but taking in account that the map has the form of [string]sql.NullString, you'll need to cast your value field to be sql.NullString (a nullable string, default strings in golang are not nullable.) To do this you can write a function to do accomplish that job:

//ToNullString invalidates a sql.NullString if empty, validates if not empty
func ToNullString(s string) sql.NullString {
    return sql.NullString{String: s, Valid: s != ""}
}

Now, you can do something like this:

data := hstore.Hstore{}
data.Map["data"] = ToNullString("data")

_, err = db.Exec(`INSERT INTO table(id, my_field) VALUES ($1, $2)`, data, id)

Then you can update the value of your hstore performing this:

data.Map["data"] = ToNullString("dat")
_, err := postgresConn.Exec(`UPDATE table SET my_field = my_field || $1 WHERE id = $2`, data, id)

Note that using update will update the map in Hstore, that means that if you just change the value part, it will update the value; but if you change the key and the value, it will add a new pair of (key, value) in your hstore instead of replacing the old ones.

To clarify it a little bit, I put a sample code to test how it works, this code should work with your parameters, I've just changed table to tabl in order to respect postgreSQL keywords:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/lib/pq"
    "github.com/lib/pq/hstore"
)

//ToNullString invalidates a sql.NullString if empty, validates if not empty
func ToNullString(s string) sql.NullString {
    return sql.NullString{String: s, Valid: s != ""}
}

func main() {
    var err error //To handle different errors
    postgresConn, _ := sql.Open("postgres", os.Getenv("DB_CONN_URL"))

    data := hstore.Hstore{}
    data.Map = make(map[string]sql.NullString)


    //Inserting the first element with:
    //id: "024b54f2-a477-4715-984c-896bf0446dcf"
    //my_field :"data => data"
    id := "024b54f2-a477-4715-984c-896bf0446dcf"
    data.Map["data"] = ToNullString("data")
    _, err = postgresConn.Exec(`INSERT INTO tabl(id, my_field) VALUES ($2, $1)`, data, id)
    if err != nil {
        fmt.Println(err)
    }

    //Adding a second field in hstore:
    //id: "024b54f2-a477-4715-984c-896bf0446dcf"
    //my_field :"data => data", "data2 => more_data"
    data.Map["data2"] = ToNullString("more_data")
    _, err = postgresConn.Exec(`UPDATE tabl SET my_field = my_field || $1 WHERE id = $2`, data, id)
    if err != nil {
        fmt.Println(err)
    }

    //Modifying the first value field:
    //id: "024b54f2-a477-4715-984c-896bf0446dcf"
    //my_field :"data => value, data2 => more_data"
    data.Map["data"] = ToNullString("value")
    _, err = postgresConn.Exec(`UPDATE tabl SET my_field = my_field || $1 WHERE id = $2`, data, id)
    if err != nil {
        fmt.Println(err)
    }
}

To get more information in order to work with hstore from a sql perspective, you can take a look here.

PD: Don't use table as a tablename because it's a reserved word.

PD2: I'm using postgresConn.Exec() function only for the demonstration, change it if you need prepared statements, more information when to use Exec() or Query() here.

PD3: When using a map, try to not use the same value for the key and the value.

How to insert a hstore object using lib/pq to postgres

1 Answers1