PostgreSQL list parameter in Go (using database/SQL and pq)

huangapple go评论91阅读模式
英文:

PostgreSQL list parameter in Go (using database/SQL and pq)

问题

我正在尝试编写一个查询,该查询接受一个列表参数(即一个包含多个值的参数)。在PostgreSQL中,似乎至少有时候是可以实现的(https://stackoverflow.com/a/10829760/836390)。我想要的是像这样的查询语句:

rows, err := db.Query("SELECT * FROM table WHERE id in $1", []int{1, 2, 3})

然而,当我使用pq驱动执行时,出现了错误:

sql: converting Exec argument #0's type: unsupported type []int, a slice

这是因为pq驱动还不支持这种方式,还是database/sql不支持,或者根本不支持在PostgreSQL中使用这种方式呢?

英文:

I'm trying to write a query that takes a list parameter (ie, a single parameter which is a list of values). It appears that this is at least sometimes possible in PostgreSQL (https://stackoverflow.com/a/10829760/836390). What I want is something like this:

rows, err := db.Query("SELECT * FROM table WHERE id in $1", []int{1, 2, 3})

However, when I execute this using the pq driver, I get an error:

sql: converting Exec argument #0's type: unsupported type []int, a slice

Is this simply not supported in pq yet, or is this not supported in database/sql, or not in PostgreSQL at all, or what?

答案1

得分: 9

你现在可以使用pq.Array和切片参数。所以查询语句看起来像这样:

rows, err := db.Query("SELECT * FROM table WHERE id in $1", pq.Array([]int{1, 2, 3}))
英文:

You can use pq.Array with slice parameters nowadays. So the query would look like:

rows, err := db.Query("SELECT * FROM table WHERE id in $1", pq.Array([]int{1, 2, 3}))

答案2

得分: 6

看起来pq使用了database/sql的默认ValueConverter,该转换器无法处理切片(请参阅文档中的DefaultParameterConverter)。

英文:

So it looks like pq uses database/sql's default ValueConverter, which has no ability to handle slices (see the documentation for DefaultParameterConverter).

答案3

得分: 3

我无法获得被接受的答案,因为出现了语法错误。我稍微修改了答案,并使其对我起作用。

我使用的资源是pq.Array函数的文档。

rows, err := db.Query("SELECT * FROM table WHERE id = ANY($1)", pq.Array([]int{1, 2, 3}))
英文:

I couldn't get the accepted answer to work due to a syntax error. I modified the answer a bit and got it to work for me.

The resource I used was the pq.Array function documentation.

rows, err := db.Query("SELECT * FROM table WHERE id = ANY($1)", pq.Array([]int{1, 2, 3}))

答案4

得分: 1

请尝试使用另一种Postgres客户端:https://github.com/vmihailenco/pg

阅读文档可以了解其对数组的支持,并包含了使用切片的示例。

_, err := db.Query(users,
    `WITH users (name, emails) AS (VALUES (?, ?), (?, ?))
    SELECT * FROM users`,
    "admin", []string{"admin1@admin", "admin2@admin"},
    "root", []string{"root1@root", "root2@root"},
)

(我自己没有使用过,但是初步看起来很有潜力)。

英文:

Look at using an alternative Postgres client: https://github.com/vmihailenco/pg

The readme details array support and includes an example of using a slice.

    _, err := db.Query(users,
    `WITH users (name, emails) AS (VALUES (?, ?), (?, ?))
    SELECT * FROM users`,
    "admin", []string{"admin1@admin", "admin2@admin"},
    "root", []string{"root1@root", "root2@root"},
)

(I've not used this myself, but a look over it shows promise).

答案5

得分: 0

这确实看起来有些“反复无常”。这里有两种尝试的方法:

rows, err := db.Query("SELECT * FROM table WHERE id in ($1)", []int{1, 2, 3})

或者

rows, err := db.Query("SELECT * FROM table WHERE id = ANY($1::[]int)", []int{1, 2, 3})

英文:

This does in fact appear to be 'temperamental'. Here are two I would attempt:

rows, err := db.Query("SELECT * FROM table WHERE id in ($1)", []int{1, 2, 3})

or

rows, err := db.Query("SELECT * FROM table WHERE id = ANY($1::[]int)", []int{1, 2, 3})

huangapple
  • 本文由 发表于 2014年1月5日 07:49:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/20928212.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定