4

This may be a duplicate of SELECT * FROM X WHERE id IN (...) with Dapper ORM

I am trying to achieve :

connection.execute("delete from table where id in @ids", new { ids = new int[]{1,2}});

But it's not working. I always get : ERROR: 42883: operator does not exist: integer = integer[].

Even if I do this :

connection.Query<a>("select * from a where a_id in @ids", new { ids = new int[] { 12, 13 } })

I get the same exception. I am accessing a postgresql database with Npgsql. Can you tell me what i am doing wrong ?

Here's what happens at the database for the second statement :

Here's some log for the second statement :

operator does not exist: integer = integer[] at character 33

No operator matches the given name and argument type(s). You might need to add explicit type casts.

select * from a where a_id in ((array[12,13])::int4[])

And this is for the first one (same as above but the last line is different)

delete from a where a_id in ((array[12,13])::int4[])

user3448717
  • 73
  • 1
  • 6
  • That's really odd... I can't see anything wrong with your c#. Do you perhaps have a SQL trace of what actually went to the server? – Marc Gravell Mar 22 '14 at 08:29
  • I've updated the question with some statements from the database – user3448717 Mar 22 '14 at 15:07
  • This is the second report of this I've seen in recent days... I have a strong suspicion that it is actually the specific ado.net provider (postgres) that is doing this, because that simply isn't the TSQL we generate... I will have to investigate – Marc Gravell Mar 22 '14 at 15:17
  • Thanks a lot, in the meanwhile, I'll put the feature on standby. Can you point me where I can find the progress of the investigation ? – user3448717 Mar 22 '14 at 15:19
  • Related: http://stackoverflow.com/questions/25297173/dapper-adddynamicparams-for-in-query-with-ienumerable-parameter-in-postgres. Probably needs to be `where id = ANY @ids` – wrschneider Jun 21 '16 at 01:20
  • Possible duplicate of [Dapper AddDynamicParams for IN query with IEnumerable parameter in Postgres](http://stackoverflow.com/questions/25297173/dapper-adddynamicparams-for-in-query-with-ienumerable-parameter-in-postgres) – Uyghur Lives Matter Jan 31 '17 at 15:47

3 Answers3

3

I advise you to take look at the Postgres docs Searching in Arrays. In brief, you should use the operator "ANY" or "ALL" or manually check the column for each value of the array.

This sql is the equivalent version of a query with the IN clause:

delete from table where id = any (@ids)
Darion Badlydone
  • 897
  • 14
  • 37
  • Please add some explanation of why/how this code helps the OP. This will help provide an answer future viewers can learn from. See [this Meta question and its answers](http://meta.stackoverflow.com/q/256359/215552) for more information. – Heretic Monkey Jan 31 '17 at 22:18
0
var query = "SELECT * FROM lookup WHERE LOWER(discriminator) = ANY(@types)";
_connection.QueryAsync<Lookup>(query, new { types = new[] {"Prefix", "Suffix"} });

This works for me

tt-top jo
  • 1
  • 2
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 08 '21 at 15:45
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30535329) – Sandeep Kushwah Dec 08 '21 at 20:20
-1

What worked for me: connection.Query("select * from a where a_id = ANY(@ids)", new { ids = new int[] { 12, 13 } })

Sandeep Tripathi
  • 77
  • 2
  • 9