Array Query Parameter with IN operator

[Cosrnos]

I have a query where I'm trying to retrieve a list of posts that have a post type that is in an array. For example...

SELECT * FROM posts WHERE post_type IN ('post', 'comment')

I'm trying to use query parameters for this so I started off with the following:

SELECT * FROM posts WHERE post_type IN $1

and pass in the array ['post', 'comment']. However I get an error stating there's a problem with my syntax near $1. I tried wrapping the $1 in parenthesis and while this query executed, it returned no rows while my original query (without parameters) would return at least 5 rows.

I'm wondering what PG does with array parameters that's causing this query to return 0 rows. I would expect that if I passed an array as a parameter that I should be able to use it with an IN operator. Am I missing something or is this perhaps a bug?

[brianc]

I'd need to see the code you're running to give you an exact answer but in general there is no way to do an inlist clause on a single parameter. You have to build the inlist clause manually. There are plenty of sql building libraries to do this for you, but it would end up looking like this:

SELECT * FROM posts WHERE post_type IN ($1, $2)

Otherwise you can do an ANY clause which does work on a single array parameter. Then you'd need to do something like this:

client.query('SELECT * FROM posts WHERE post_type ANY($1), [['post', 'comment']], ...)`

Notice the double array because you're passing an entire array as a single parameter. The syntax isn't 100% above as I'm doing this off the top of my head.

[erayhanoglu]

Hi,
There is an other problem about this issue.
using ANY($1) works fine only if array has one dimension. Think that we want to query from a table with 2 keys fields. So we need arrays of array.

The query below works:
select * from my_table where (key1, key2) in (('a1','a2), ('b1','b2))

But if we want to use parameters, like below:
client.query('select * from my_table where (key1, key2) in ANY($1)', [[ ['a1', 'b2], ['a2', 'b2]]]]) returns input of anonymous composite types is not implemented error.

[tonyxiao]

Out of curiosity, does typeorm do the building inlist clause manually as specified in @brianc 's post above? cc @pleerock