dapper sql in-clause throws exception

dapper in-clause postgresql sql

Question

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[])

Popular Answer

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)



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why