sql.Scan error with PostgreSQL TEXT[] array: "unsupported Scan, storing driver.Value type string into type *[]string" when using pgx/v4 with database/sql

[dapoadedire]

I'm getting a scan error when trying to retrieve a PostgreSQL TEXT[] array column using database/sql with the pgx/v4/stdlib driver. I'm currently using pgx/v4 and would prefer not to change my database driver if possible.

Error:

{
  "error": "sql: Scan error on column index 4, name \"tags\": unsupported Scan, storing driver.Value type string into type *[]string"
}

Database Schema:

CREATE TABLE IF NOT EXISTS posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    tags TEXT[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Go Struct:

type Post struct {
    ID        int64    `json:"id"`
    Title     string   `json:"title"`
    Content   string   `json:"content"`
    UserID    int64    `json:"user_id"`
    Tags      []string `json:"tags"`
    CreatedAt string   `json:"created_at"`
    UpdatedAt string   `json:"updated_at"`
}

Code that fails (GetByID method):

func (s *PostStore) GetByID(ctx context.Context, id int64) (*Post, error) {
    query := `
    SELECT id, title, content, user_id, tags, created_at, updated_at
    FROM posts
    WHERE id = $1
    `
    post := &Post{}
    err := s.db.QueryRowContext(ctx, query, id).Scan(
        &post.ID,
        &post.Title,
        &post.Content,
        &post.UserID,
        &post.Tags, 
        &post.CreatedAt,
        &post.UpdatedAt,
    )
    if err != nil {
        return nil, err
    }
    return post, nil
}

What I've tried:

• The Create method works fine when inserting arrays into the database
• Only the retrieval (Scan) operation fails

How can I properly scan PostgreSQL TEXT[] arrays into Go []string slices when using database/sql with pgx/v5/stdlib? Is there a way to handle this without switching to the native pgx driver or changing my database schema?

Any help would be greatly appreciated!

[jackc]

v4 is no longer supported and I would recommend upgrading to v5. But if I recall correctly, the way you would do this in v4 is with the pgtype.TextArray.

In v5 you would use https://pkg.go.dev/github.com/jackc/pgx/v5@v5.7.6/pgtype#Map.SQLScanner.

Unfortunately, in neither version can you directly scan to a []string. However, thanks to golang/go#67546, in future versions of Go and pgx you will be able to directly scan to []string and other slice types directly with database/sql / stdlib.

[dapoadedire]

Thank you! I upgraded to v5 and using pgtype.Map.SQLScanner works perfectly.

Here's the working implementation:

func (s *PostStore) GetByID(ctx context.Context, id int64) (*Post, error) {
    query := `
    SELECT id, title, content, user_id, tags, created_at, updated_at
    FROM posts
    WHERE id = $1
    `
    var post Post
    m := pgtype.NewMap()

    err := s.db.QueryRowContext(ctx, query, id).Scan(
        &post.ID,
        &post.Title,
        &post.Content,
        &post.UserID,
        m.SQLScanner(&post.Tags),
        &post.CreatedAt,
        &post.UpdatedAt,
    )
    if err != nil {
        switch {
        case errors.Is(err, sql.ErrNoRows):
            return nil, ErrNotFound
        }
        return nil, err
    }

    return &post, nil
}