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

Question

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?

score 9 · Accepted Answer · answered Aug 21 '18 at 12:46

9

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}))

answered Aug 21 '18 at 12:46

hullarb

126
1
2

score 6 · Answer 2 · answered Jan 05 '14 at 01:02

6

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

answered Jan 05 '14 at 01:02

joshlf

21,822
11
69
96

score 3 · Answer 3 · answered Jan 27 '20 at 16:51

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}))

score 1 · Answer 4 · answered Jan 05 '14 at 05:38

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).

score 0 · Answer 5 · answered Jan 05 '14 at 00:14

0

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})

answered Jan 05 '14 at 00:14

Sorry, neither of those work. My suspicion is that the "unsupported type" error is coming from the Go side, prior to processing the query string itself (In Postgres, query parameters are processed by the Postgres server, which means that the client never constructs a query from parameters, but simply sends the query raw and sends the parameters separately). If it was coming from the server, the error wouldn't have used the Go syntax and terminology, "[]int" and "slice." – joshlf Jan 05 '14 at 00:28
Can you get the exact string that is being executed on the server ('mogrify' it?) – Jan 05 '14 at 00:33
It's never making it to the server. – joshlf Jan 05 '14 at 00:41
OK, I figured it out. See my answer. – joshlf Jan 05 '14 at 01:02

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

5 Answers5

Linked