21

I have an array of strings, and I'd like to have a query containing an IN clause, like:

"... WHERE t.name IN ('foo', 'bar', 'baz')..>"

Here's the final bit of my query, which contains a "where X in Y" clause:

...
left join genre_tag_band_join tj on hb.id = tj.band_id or ob.id = tj.band_id
left join genre_tags t on tj.genre_tag_id = t.id
inner join venues v on e.venue_id = v.id

where t.name IN @tagsParam...

I make a Dapper call like this

var shows = con.Query<Event, Band, Band, GenreTag, Venue, Event>(query, (e, hb, ob, gt, v) =>
{
    Event show;
    ...
    return e;
},
new { tagsParam = tagsArr}).AsQueryable();

where tagsArr is a string[].

I get exception:

{"42601: syntax error at or near \"$1\""}

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
Sava B.
  • 1,007
  • 1
  • 10
  • 21

1 Answers1

31

In PostgreSQL, you can't use IN to check whether a value is inside an array, you have to use the following PostgreSQL-specific syntax: where t.name = ANY (@tagsParam). See the section 8.15.5 in the PostgreSQL docs.

Dan
  • 3,229
  • 2
  • 21
  • 34
Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • Hm, sill no luck when I do `...and t.name = ANY @tagsParam`. I think postgresql does support "IN": http://www.postgresqltutorial.com/postgresql-in/. When I run a query like `...inner join venues v on e.venue_id = v.id where (e.date > current_date) and t.name in ('foo')...` in pgadmin, it does work.. – Sava B. Oct 18 '15 at 13:51
  • 4
    PostgreSQL supports IN, but not with array types - (1, 2) is not an array type. With the ANY syntax, can you try surrounding @tagParam with parentheses? – Shay Rojansky Oct 18 '15 at 13:53
  • 2
    This is confusing because you normally expect Dapper to rewrite `where foo in (@array)` to `where foo in (@a1, @a2, ...) ` but it does not do that for PostgreSQL for some reason. – wrschneider Jun 21 '16 at 01:23
  • I totally lost faith in Postgres right now :D – Renato Medeiros May 13 '21 at 12:37