postgres npgsql How can I make integer array work correctly

Question

I have a simple sql query that can take in multiple arrays of type Integer, if I do something like this it works r.userid in(36,37,38) I left out some of the code since everything works correctly unless I try to pass it in as a List . I am new to npgsql so I am sure I am missing something any suggestions would be great .

This works

select s.post,s.fullname,s.location,s.streetplace,s.streetplace_id,s.profile_id as 
    ,s.link,s.title FROM streams s JOIN reshares r on r.userid in(36,37,38)

however if I do something like this

This does not work

var newList = new List<int>{ 36,37,38 };

   select s.post,s.fullname,s.location,s.streetplace,s.streetplace_id,s.profile_id as 
        ,s.link,s.title FROM streams s JOIN reshares r on r.userid in(@following)
  cmd.Parameters.AddWithValue("@following", newList);

I get the error

42883: operator does not exist: integer = integer[] at Npgsql.NpgsqlConnector.

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

Does this answer your question? Check if value exists in Postgres array — madreflection
– madreflection, Commented Apr 14, 2021 at 18:52

Shay Rojansky · Accepted Answer · 2021-04-14 18:56:30Z

2

In PostgreSQL, the IN operator is for rows, not arrays; to accomplish the same with arrays, you can use the ANY construct: WHERE x = ANY(@array).

Consult the full PostgreSQL docs here.

answered Apr 14, 2021 at 18:56

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1591668 Over a year ago

Thank you, that is exactly what was needed and will also look at docs.

Collectives™ on Stack Overflow

postgres npgsql How can I make integer array work correctly

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related