Golang操作Postgres数组报错：无法扫描至data.Image类型

错误信息 #

sql: Scan error on column index 8, name "image_paths": pq: parsing array element index 0: pq: scanning to data.Image is not implemented; only sql.Scanner

数据库记录 #

相关代码 #

func (s SpaceDetailsModel) GetForUser(filters Filters, id int64) ([]*SpaceDetails, Metadata, error) {
    if id < 1 {
        return nil, Metadata{}, ErrRecordNotFound
    }

    query := `
        SELECT count(*) OVER(), id, created_At, space_owner_id, location_text, post_code, longitude, latitude, image_paths, space_description, booking_count, number_of_reviews, average_rating, version
        FROM spaces
        WHERE space_owner_id = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3`

    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    args := []interface{}{id, filters.limit(), filters.offset()}

    rows, err := s.DB.QueryContext(ctx, query, args...)
    if err != nil {
        return nil, Metadata{}, err
    }

    defer rows.Close()

    totalRecords := 0
    spaces := []*SpaceDetails{}

    for rows.Next() {
        var space SpaceDetails

        err := rows.Scan(
            &totalRecords,
            &space.ID,
            &space.CreatedAt,
            &space.SpaceOwnerId,
            &space.LocationText,
            &space.PostCode,
            &space.Coordinates.Longitude,
            &space.Coordinates.Latitude,
            pq.Array(&space.Images),
            &space.SpaceDescription,
            &space.BookingCount,
            &space.NumberOfReviews,
            &space.AverageRating,
            &space.Version,
        )
        if err != nil {
            return nil, Metadata{}, err
        }

        spaces = append(spaces, &space)
    }

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

    metadata := calculateMetadata(totalRecords, filters.Page, filters.PageSize)

    return spaces, metadata, nil
}

堆栈跟踪 #

PS C:\Users\bcart\OneDrive\Desktop\Project\Project Z\Code\Parkit> 
go run ./cmd/api
{"level":"INFO","time":"2022-07-28T13:40:14Z","message":"database connection pool established"}
{"level":"INFO","time":"2022-07-28T13:40:14Z","message":"starting server","properties":{"addr":":4000","env":"development"}}
{"level":"ERROR","time":"2022-07-28T13:40:19Z","message":"sql: Scan error on column index 8, name \"image_paths\": pq: parsing array element index 0: pq: scanning to data.Image is not implemented; only sql.Scanner","properties":{"request_method":"POST","request_url":"/api/v1/days_bookings_record"},"trace":"goroutine 22 [running]:\nruntime/debug.Stack(0x31011630, 0xeda7488c3, 0x0)\n\tC:/Program Files/Go/src/runtime/debug/stack.go:24 +0xa5\nparkit/internal/jsonlog.(*Logger).print(0xc000054600, 0xc0000fe001, 0xc0000fe000, 0x99, 0xc00009cc60, 0x0, 0x0, 0x0)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/internal/jsonlog/jsonlog.go:80 +0x475\nparkit/internal/jsonlog.(*Logger).PrintError(0xc000054600, 0xbd9ba0, 0xc00009a2e0, 0xc00009cc60)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/internal/jsonlog/jsonlog.go:52 +0x69\nmain.(*application).logError(0xc0000be000, 0xc0000c6300, 0xbd9ba0, 0xc00009a2e0)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/errors.go:9 +0x145\nmain.(*application).serverErrorResponse(0xc0000be000, 0xbe03e0, 0xc0000883c0, 0xc0000c6300, 0xbd9ba0, 0xc00009a2e0)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/errors.go:26 +0x54\nmain.(*application).createDaysBookingsRecordHandler(0xc0000be000, 0xbe03e0, 0xc0000883c0, 0xc0000c6300)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/spacebookings.go:39 +0x6cd\nnet/http.HandlerFunc.ServeHTTP(0xc0000a4340, 0xbe03e0, 0xc0000883c0, 0xc0000c6300)\n\tC:/Program Files/Go/src/net/http/server.go:2069 +0x4b\ngithub.com/julienschmidt/httprouter.(*Router).Handler.func1(0xbe03e0, 0xc0000883c0, 0xc0000c6300, 0x0, 0x0, 0x0)\n\tC:/Users/bcart/go/pkg/mod/github.com/julienschmidt/httprouter@v1.3.0/router.go:275 +0x1e7\ngithub.com/julienschmidt/httprouter.(*Router).ServeHTTP(0xc000088180, 0xbe03e0, 0xc0000883c0, 0xc0000c6300)\n\tC:/Users/bcart/go/pkg/mod/github.com/julienschmidt/httprouter@v1.3.0/router.go:387 +0xc7e\nmain.(*application).authenticate.func1(0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/middleware.go:128 +0x423\nnet/http.HandlerFunc.ServeHTTP(0xc00009a0a0, 0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Program Files/Go/src/net/http/server.go:2069 +0x4b\nmain.(*application).rateLimit.func2(0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/middleware.go:84 +0x82\nnet/http.HandlerFunc.ServeHTTP(0xc00009c2a0, 0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Program Files/Go/src/net/http/server.go:2069 +0x4b\nmain.(*application).enableCORS.func1(0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/middleware.go:208 +0x3ec\nnet/http.HandlerFunc.ServeHTTP(0xc00009a0c0, 0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Program Files/Go/src/net/http/server.go:2069 +0x4b\nmain.(*application).recoverPanic.func1(0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/middleware.go:30 +0x98\nnet/http.HandlerFunc.ServeHTTP(0xc00009a0e0, 0xbe03e0, 0xc0000883c0, 0xc0000c6200)\n\tC:/Program Files/Go/src/net/http/server.go:2069 +0x4b\ngithub.com/felixge/httpsnoop.CaptureMetrics.func1(0xbe03e0, 0xc0000883c0)\n\tC:/Users/bcart/go/pkg/mod/github.com/felixge/httpsnoop@v1.0.3/capture_metrics.go:29 +0x53\ngithub.com/felixge/httpsnoop.(*Metrics).CaptureMetrics(0xc0000960a8, 0xbdfe70, 0xc0000c80e0, 0xc000073ab0)\n\tC:/Users/bcart/go/pkg/mod/github.com/felixge/httpsnoop@v1.0.3/capture_metrics.go:84 +0x1e7\ngithub.com/felixge/httpsnoop.CaptureMetricsFn(...)\n\tC:/Users/bcart/go/pkg/mod/github.com/felixge/httpsnoop@v1.0.3/capture_metrics.go:39\ngithub.com/felixge/httpsnoop.CaptureMetrics(0xbda7e0, 0xc00009a0e0, 0xbdfe70, 0xc0000c80e0, 0xc0000c6200, 0x970ca7, 0xc0000aa2ec, 0x1e0edbf0778)\n\tC:/Users/bcart/go/pkg/mod/github.com/felixge/httpsnoop@v1.0.3/capture_metrics.go:28 +0xae\nmain.(*application).metrics.func1(0xbdfe70, 0xc0000c80e0, 0xc0000c6200)\n\tC:/Users/bcart/OneDrive/Desktop/Project/Project Z/Code/Parkit/cmd/api/middleware.go:223 +0x87\nnet/http.HandlerFunc.ServeHTTP(0xc0000aa0c0, 0xbdfe70, 0xc0000c80e0, 0xc0000c6200)\n\tC:/Program Files/Go/src/net/http/server.go:2069 +0x4b\nnet/http.serverHandler.ServeHTTP(0xc0000c8000, 0xbdfe70, 0xc0000c80e0, 0xc0000c6200)\n\tC:/Program Files/Go/src/net/http/server.go:2887 +0xaa\nnet/http.(*conn).serve(0xc00008a140, 0xbe0f20, 0xc0000aa240)\n\tC:/Program Files/Go/src/net/http/server.go:1952 +0x8cd\ncreated by net/http.(*Server).Serve\n\tC:/Program Files/Go/src/net/http/server.go:3013 +0x3b8\n"}
2022/07/28 14:40:19 http: superfluous response.WriteHeader call from github.com/felixge/httpsnoop.(*Metrics).CaptureMetrics.func1.1 (capture_metrics.go:53)

原因分析 #

错误核心是：Postgres的pq.Array无法直接将数组元素映射到自定义的data.Image结构体，因为该结构体没有实现sql.Scanner接口，pq驱动不知道如何将数据库中的字符串（从图片链接数组来看，数据库里是文本数组）转换为data.Image实例。

解决方法 #

方法一：让data.Image实现sql.Scanner接口 #

假设data.Image结构体定义如下：

package data

type Image struct {
    URL string
}

为其实现Scan方法：

func (i *Image) Scan(value interface{}) error {
    str, ok := value.(string)
    if !ok {
        return fmt.Errorf("expected string, got %T", value)
    }
    i.URL = str
    return nil
}

实现后，pq驱动就能自动将数组中的每个字符串元素转换为Image实例，原代码中的pq.Array(&space.Images)可以正常使用。

方法二：先扫描到字符串切片，再手动转换 #

如果不想修改Image结构体，可以临时用字符串切片接收数据，再转换为Image切片：
修改rows.Scan部分代码：

var imagePaths []string
err := rows.Scan(
    &totalRecords,
    &space.ID,
    &space.CreatedAt,
    &space.SpaceOwnerId,
    &space.LocationText,
    &space.PostCode,
    &space.Coordinates.Longitude,
    &space.Coordinates.Latitude,
    pq.Array(&imagePaths), // 先扫描到字符串切片
    &space.SpaceDescription,
    &space.BookingCount,
    &space.NumberOfReviews,
    &space.AverageRating,
    &space.Version,
)
if err != nil {
    return nil, Metadata{}, err
}
// 手动转换为Image切片
space.Images = make([]data.Image, len(imagePaths))
for idx, path := range imagePaths {
    space.Images[idx] = data.Image{URL: path}
}

内容的提问来源于stack exchange，提问作者Bryan