postgresql - 转换参数 $1 类型 : unsupported type []interface {}, 的一个接口(interface)

Question

这个问题在这里已经有了答案:





sql: converting argument $1 type: unsupported type []int, a slice of in

(3 个回答)


2年前关闭。




我尝试查询用户 ID 列表。我正在使用 Go Pq图书馆。

func ListUsers(userIDs []interface{}) (users []User, err error) {
    stm := `SELECT
    U.id,
    (
        CASE WHEN TRIM(UP.first_name || ' ' || UP.last_name) = '' THEN U.id :: TEXT ELSE UP.first_name || ' ' || UP.last_name END
        ) as avatar_name,
FROM
    users AS U
        JOIN user_profiles UP ON UP.user_id = U.id
WHERE
        U.id IN ($1);`

    rows, err := postgresql.Instance.Query(stm, userIDs)
    defer rows.Close()
    if err != nil && err != sql.ErrNoRows {
        return
    }

    if err == sql.ErrNoRows {
        return users, nil
    }

    for rows.Next() {
        var (
            id         int
            AvatarName string
        )
        err = rows.Scan(&id, &AvatarName)
        if err != nil {
            return nil, err
        }

        users = append(users, User{ID: id, AvatarName: AvatarName})
    }

    return users, nil
}

当我运行它时，它会给出错误 转换参数 $1 类型:不支持的类型 []interface {}，接口(interface) slice

Answer 1

用 pq.Array 包裹 slice .

更改IN ($1)至= any($1)所以可以使用数组值。

这是代码:

    stm := `SELECT
    U.id,
    (
        CASE WHEN TRIM(UP.first_name || ' ' || UP.last_name) = '' THEN U.id :: TEXT ELSE UP.first_name || ' ' || UP.last_name END
        ) as avatar_name,
FROM
    users AS U
        JOIN user_profiles UP ON UP.user_id = U.id
WHERE
        U.id = any($1);`
    rows, err := postgresql.Instance.Query(stm, pq.Array(userIDs))