current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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 Postgres (http://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 Postgres at all, or what? Thanks!

share|improve this question

3 Answers 3

up vote 1 down vote

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

share|improve this answer
up vote 0 down vote

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

share|improve this answer
    
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." –  synful Jan 5 at 0:28
    
Can you get the exact string that is being executed on the server ('mogrify' it?) –  user2839610 Jan 5 at 0:33
    
It's never making it to the server. –  synful Jan 5 at 0:41
    
See this SO answer. –  synful Jan 5 at 0:42
    
OK, I figured it out. See my answer. –  synful Jan 5 at 1:02
up vote 0 down vote accepted

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

share|improve this answer
    
You should mark this as the answer for others. –  user2839610 Jan 5 at 1:03
    
SO won't let me for 2 days. –  synful Jan 5 at 2:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.