Working with []text

[BigBoulard]

Hi,

First off thank you for the time you take to answer to all these questions, it's impressive.

I'm trying to insert a slice of items and get then back in a Go Struct. Each Item has pictures that is a struct field of type []pgtype.Text (it may be not the good type to be chosen).

I can insert the items and their pictures in Postgres, but I get an error when trying to getting it back to the []pgtype.Text

can't scan into dest[8]: unable to assign to *pgtype.Text

The type of the Postgres field is []text.

Thank you so much for your help (don't hesitate to point out anything weird in the code it's gold to me ;) )

type Item struct {
Uid pgtype.Text   `json:"uid" sql:"uid"`
...
Pictures     []pgtype.Text `json:"pictures" sql:"pictures"`
...
}

The SELECT part

    getQuery := "SELECT uid, ... , pictures FROM items WHERE uid = $1"
    rows, err := postgres.GetSession().Query(context.Background(), getQuery, uid)

    var res = []Item{}
    for rows.Next() {
        c := &Item{}
        err = rows.Scan(&c.Uid, ... , &c.Pictures)
        if err != nil {
            return nil, err // ERROR HERE can't scan into dest[8]: unable to assign to *pgtype.Text
        }
        res = append(res, *c)
    }

    if rows.Err() != nil {
        return nil, err)
    }

    return res, nil
}

The INSERT part

func PutItems(uid string, items []Items) err {
    nb_fields := 8
    valueStrings := make([]string, 0, len(items))
    valueArgs := make([]interface{}, 0, len(items)*nb_fields)

    for idx, item := range items {
        valueStrings = append(valueStrings, fmt.Sprintf("($%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d)",
            idx*nb_fields+1, idx*nb_fields+2, idx*nb_fields+3, idx*nb_fields+4, idx*nb_fields+5, idx*nb_fields+6, idx*nb_fields+7, idx*nb_fields+8))
        valueArgs = append(valueArgs, item.Uid.String, ...item.Pictures)
    }

    stmt := fmt.Sprintf("INSERT INTO items(uid, ... , pictures) VALUES %s",
        strings.Join(valueStrings, ","))

    _, err := postgres.GetSession().Exec(context.Background(), stmt, valueArgs...)
    if err != nil {
        return err
    }
    return nil
}

The connection part

package postgres

import (
    "context"
    "fmt"
    "log"
    "os"

    "github.com/jackc/pgx/v4/pgxpool"
    "github.com/joho/godotenv"
)

type ConnString struct {
    DBName   string
    User     string
    Pass     string
    Host     string
    Protocol string
    SSLMode  string
}

var dbpool *pgxpool.Pool

func GetSession() *pgxpool.Pool {
    return dbpool
}

func init() {
    connString := loadConnString()
    connStr := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=%s",
        connString.User,
        connString.Pass,
        connString.Host,
        connString.DBName,
        connString.SSLMode)
    var err error

    dbpool, err = pgxpool.Connect(context.Background(), connStr)
    if err != nil {
        panic(err)
    }
    // defer db.Close() -> NOT SURE HERE

    fmt.Println("You are connected to your database.")
}

func loadConnString() ConnString {
    err := godotenv.Load()

    if err != nil {
        log.Fatal("Error loading .env file")
    }

    connString := ConnString{
        DBName:   os.Getenv("PG_DBName"),
        User:     os.Getenv("PG_USER"),
        Pass:     os.Getenv("PG_PASS"),
        Host:     os.Getenv("PG_HOST"),
        Protocol: os.Getenv("PG_Protocol"),
        SSLMode:  os.Getenv("PG_SSLmode"),
    }
    return connString
}

[jackc]

Unless you need to support NULL elements in your pictures array I suggest using a []string directly instead of any pgtype type. If you do need NULL try using a pgtype.TextArray.

If one of those doesn't fix it see if you can reduce the problem to a standalone example.

[BigBoulard]

works! Thank you again