使用Golang+SQLC+PostgreSQL查询返回空记录问题排查

基于Golang后端API实现分页功能，通过SQLC抽象操作PostgreSQL数据库，当前遇到查询返回空记录的问题。直接在psql/pgAdmin中执行对应SQL语句可正常获取所有记录，但Golang代码返回空结果。相关信息如下：

SQLC代码 #

const getAllFeeds = `-- name: GetAllFeeds :many
SELECT count(*) OVER(), id, created_at, updated_at, name, url, user_id, version
FROM feeds
WHERE ($1 = '' OR to_tsvector('simple', name) @@ plainto_tsquery('simple', $1))
AND ($2 = '' OR url LIKE '%' || $2 || '%')
ORDER BY id ASC
LIMIT $3 OFFSET $4
`

type GetAllFeedsParams struct {
    Column1 interface{}
    Column2 interface{}
    Limit   int32
    Offset  int32
}

type GetAllFeedsRow struct {
    Count     int64
    ID        uuid.UUID
    CreatedAt time.Time
    UpdatedAt time.Time
    Name      string
    Url       string
    UserID    int64
    Version   int32
}

func (q *Queries) GetAllFeeds(ctx context.Context, arg GetAllFeedsParams) ([]GetAllFeedsRow, error) {
    rows, err := q.db.QueryContext(ctx, getAllFeeds,
        arg.Column1,
        arg.Column2,
        arg.Limit,
        arg.Offset,
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []GetAllFeedsRow
    for rows.Next() {
        var i GetAllFeedsRow
        if err := rows.Scan(
            &i.Count,
            &i.ID,
            &i.CreatedAt,
            &i.UpdatedAt,
            &i.Name,
            &i.Url,
            &i.UserID,
            &i.Version,
        ); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    if err := rows.Close(); err != nil {
        return nil, err
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return items, nil
}

Golang业务代码 #

func (m FeedModel) GetAllFeeds(name string, url string, filters Filters) ([]*Feed, Metadata, error) {
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    fmt.Printf("Executing GetAllFeeds with name: [%s], url: [%s], limit: %d, offset: %d\n",
        name, url, filters.limit(), filters.offset())

    rows, err := m.DB.GetAllFeeds(ctx, database.GetAllFeedsParams{
        Column1: name,
        Column2: sql.NullString{String: url, Valid: url != ""}, // Convert string to sql.NullString
        Limit:   int32(filters.limit()),
        Offset:  int32(filters.offset()),
    })
    if err != nil {
        return nil, Metadata{}, err
    }
    fmt.Println("Rows: ", rows)
    totalRecords := 0
    feeds := []*Feed{}
    for _, row := range rows {
        var feed Feed
        totalRecords = int(row.Count)
        feed.ID = row.ID
        feed.CreatedAt = row.CreatedAt
        feed.UpdatedAt = row.UpdatedAt
        feed.Name = row.Name
        feed.Url = row.Url
        feed.Version = row.Version
        feed.UserID = row.UserID
        feeds = append(feeds, &feed)
    }
    metadata := calculateMetadata(totalRecords, filters.Page, filters.PageSize)
    return feeds, metadata, nil
}

数据表结构 #

-- +goose Up
CREATE TABLE feeds(
    id UUID PRIMARY KEY,
    created_at timestamp(0) with time zone NOT NULL DEFAULT NOW(),
    updated_at timestamp(0) with time zone NOT NULL DEFAULT NOW(),
    name TEXT NOT NULL,
    url TEXT UNIQUE NOT NULL,
    version INT NOT NULL DEFAULT 1,
    user_id bigserial NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

测试情况 #

• 示例输出：Executing GetAllFeeds with name: [], url: [], limit: 30, offset: 0 Rows: [] Total Records: 0
• 直接在psql执行以下SQL可正常获取记录：

SELECT count(*) OVER(), id, created_at, updated_at, name, url, user_id, version
FROM feeds
WHERE (to_tsvector('simple', name) @@ plainto_tsquery('simple','' ) OR  ''= '')
AND (url LIKE '%' || '' || '%' OR '' = '')
ORDER BY id ASC
LIMIT 30 OFFSET 0;

核心问题 #

参数类型不匹配导致过滤条件失效：

• Golang代码中给Column2传入sql.NullString类型，当url为空时，Valid为false，PostgreSQL会将该参数解析为NULL而非空字符串''。
• SQL条件($2 = '' OR url LIKE '%' || $2 || '%')中，$2 = ''对NULL的判断结果为NULL（不满足），url LIKE '%' || NULL || '%'结果也为NULL，导致整个AND条件不成立，无记录匹配。

修复方案 #

方案1：统一传递字符串类型（推荐） #

修改Golang代码的参数传递逻辑，直接传入url字符串，无需使用sql.NullString：

rows, err := m.DB.GetAllFeeds(ctx, database.GetAllFeedsParams{
    Column1: name,
    Column2: url, // 直接传递原始字符串
    Limit:   int32(filters.limit()),
    Offset:  int32(filters.offset()),
})

同时建议给SQLC的参数结构体指定明确类型，避免interface{}的隐式类型问题：

type GetAllFeedsParams struct {
    Column1 string // 替换interface{}为string
    Column2 string // 替换interface{}为string
    Limit   int32
    Offset  int32
}

方案2：修改SQL条件兼容NULL #

如果必须使用sql.NullString，需要调整SQL的WHERE条件，增加对NULL的判断：

const getAllFeeds = `-- name: GetAllFeeds :many
SELECT count(*) OVER(), id, created_at, updated_at, name, url, user_id, version
FROM feeds
WHERE ($1 = '' OR $1 IS NULL OR to_tsvector('simple', name) @@ plainto_tsquery('simple', $1))
AND ($2 = '' OR $2 IS NULL OR url LIKE '%' || $2 || '%')
ORDER BY id ASC
LIMIT $3 OFFSET $4
`

验证 #

修改后，当url为空时，参数传递的是空字符串（或SQL兼容NULL判断），SQL条件中的$2 = ''（或$2 IS NULL）成立，会跳过url过滤逻辑，返回所有符合条件的记录。

内容的提问来源于stack exchange，提问作者B.Doe