Reading array column

[vgarvardt]

Can you pls help me understanding how to read array column using pgtype

This is the simple test code I'm trying to make work:

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgtype"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    ctx := context.TODO()

    pgxCfg, err := pgxpool.ParseConfig(os.Getenv("TEST_POSTGRES"))
    if err != nil {
        panic(fmt.Errorf("could not parse DB URL: %w", err))
    }

    pgxPool, err := pgxpool.ConnectConfig(ctx, pgxCfg)
    if err != nil {
        panic(fmt.Errorf("could not init pgx conn pool: %w", err))
    }

    if err := pgxPool.Ping(ctx); err != nil {
        panic(fmt.Errorf("could not ping DB: %w", err))
    }

    var arr pgtype.Int8Array
    if err := pgxPool.QueryRow(ctx, `SELECT ARRAY[1, 2]::bigint[]`).Scan(&arr); err != nil {
        panic(fmt.Errorf("could not read static array value: %w", err))
    }
}

result:

$ TEST_POSTGRES=postgres://test:test@localhost:52598/test?sslmode=disable go run ./test_pgx_array.go
panic: could not read static array value: can't scan into dest[0]: invalid array, expected '{': <nil>

goroutine 1 [running]:
main.main()
    /Users/vladimir.garvardt/Projects/test-pgx-array/test_pgx_array.go:31 +0x195
exit status 2

the same query running in pg:

$ docker-compose exec postgres psql -Utest -c 'SELECT ARRAY[1, 2]::bigint[]'

 array
-------
 {1,2}
(1 row)

it starts with {, so I'm doing something wrong with scanning, but can not get what exactly.

docker-compose.yml I'm using for testing:

---
version: "3"

services:
  postgres:
    image: postgres:10
    ports:
      - '5432'
    environment:
      LC_ALL: C.UTF-8
      POSTGRES_USER: test
      POSTGRES_PASSWORD: test
      POSTGRES_DB: test
    tmpfs:
      - /var/lib/postgresql/data
    healthcheck:
      test: [ "CMD", "pg_isready" ]
      interval: 10s
      timeout: 5s
      retries: 5

Thank you.

[jackc]

    "github.com/jackc/pgtype"
    "github.com/jackc/pgx/v5/pgxpool"

This is mixing pgx versions. github.com/jackc/pgtype is now part of the same repo. Use github.com/jackc/pgx/v5/pgtype.

[vgarvardt]

Thank you! This one works

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v5/pgtype"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    ctx := context.TODO()

    pgxCfg, err := pgxpool.ParseConfig(os.Getenv("TEST_POSTGRES"))
    if err != nil {
        panic(fmt.Errorf("could not parse DB URL: %w", err))
    }

    pgxPool, err := pgxpool.ConnectConfig(ctx, pgxCfg)
    if err != nil {
        panic(fmt.Errorf("could not init pgx conn pool: %w", err))
    }

    if err := pgxPool.Ping(ctx); err != nil {
        panic(fmt.Errorf("could not ping DB: %w", err))
    }

    var arr pgtype.Array[int64]
    if err := pgxPool.QueryRow(ctx, `SELECT ARRAY[1, 2]::bigint[]`).Scan(&arr); err != nil {
        panic(fmt.Errorf("could not read static array value: %w", err))
    }
    fmt.Println(arr.Elements)
}

$ TEST_POSTGRES=postgres://test:test@localhost:49856/test?sslmode=disable go run ./test.go
[1 2]