英文:
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})
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论