Support for slices

[pengux]

The documentation mentions support for slices and Postgres arrays, but when I try to insert a []string, it returns this error:

sql: converting Exec argument #2's type: unsupported type []string, a slice

So how can I work with slices and arrays?

[jackc]

Given this table:

create table slice_test(
  id serial primary key,
  strings varchar[] not null
);

Here is a sample that reads and writes Go slices into PostgreSQL arrays:

package main

import (
    "fmt"
    "log"

    "github.com/jackc/pgx"
)

func main() {
    config := pgx.ConnConfig{
        Host:     "/private/tmp",
        Database: "jack",
    }

    conn, err := pgx.Connect(config)
    if err != nil {
        log.Fatalf("Unable to establish connection: %v", err)
    }
    defer conn.Close()

    insertSlice := []string{"foo", "bar", "baz"}

    _, err = conn.Exec("insert into slice_test(strings) values($1)", insertSlice)
    if err != nil {
        log.Fatalf("Unable to insert: %v", err)
    }

    var id int32
    var selectSlice []string
    err = conn.QueryRow("select id, strings from slice_test order by id desc limit 1").Scan(&id, &selectSlice)
    if err != nil {
        log.Fatalf("Unable to select: %v", err)
    }

    fmt.Println("id:", id, "selectSlice", selectSlice)

    conn.Close()
}

[pengux]

So it should work with the stdlib package too?

[jackc]

No. This is one of the major reasons for the native pgx interface.

The database/sql interface only allows the underlying driver to return or receive the following types: int64, float64, bool, []byte, string, time.Time, or nil. The only way to handle other types is to implement the database/sql.Scanner and the database/sq/driver.Valuer interfaces. These interfaces require using the text format for transmitting values. The binary format can be substantially faster, and that is what the pgx native interface uses to encode and decode values for PostgreSQL. However, this means that the array (and other advanced types) support in pgx is not compatible with database/sql because it only understands the binary format -- not the text format that database/sql would use.

[ethanpailes]

For anyone looking for a way to do this with the stdlib interfaces, the github.com/lib/pq Scanner/Valuer wrappers work with pgx in my experience, though that library is no longer maintained.