Npgsql: How to pass a string array as parameter?

[denyss86study2]

In documentation there is no working sample for passing string array to PostgreSQL, but as far as I understand, I have to do the following:

command.CommandText = "... WHERE Id IN (:myarray)";
command.Parameters.Add("myarray", NpgsqlDbType.Array | NpgsqlDbType.Varchar).Value = getStringArray();

However, after executing this code, I receive the following error:

ERROR: 42883: operator does not exist: character varying = character varying[]

We don't want to use String.Join for this, because it may cause SQL Injections.

System settings: Npgsql 2.2.5, Windows 10, .NET 4.5.1, ASP.NET MVC

[roji]

Can you please post the full SQL query? You're setting the string parameter correctly, and the error indicates that you have a mismatch in your query (i.e. trying to insert a string array into a string column?)

[denyss86study2]

Ok, now I found what caused this error. For some reason, PostgreSQL does not allow to pass an array to IN operator and you need to use the UNNEST function:

// this will not work
SELECT * FROM mytable WHERE myId IN (:myparam)
// however this works
SELECT * FROM mytable WHERE myId IN (SELECT UNNEST(:myparam))

I have no idea why PostgreSQL made this decision.

[roji]

I think the correct/efficient way to express this in PostgreSQL is:

SELECT * FROM mytable WHERE myId = ANY(:myparam)

See http://www.postgresql.org/docs/current/static/arrays.html, section "Searching in Arrays".

EDIT: Added missing parentheses to ANY() expression

[JonathonCwik]

So I think it's been updated more recently. I can't get @roji script to work but what does work for me is:

SELECT * FROM mytable WHERE myId = ANY(:myparam)

and since any time I query for npgsql array parameter this is the first thing at the top I figured I'd like to store this here so people (e.g. me in a couple months when I do this again) don't get confused :)

[roji]

@SMASH1337 thanks for the correction, I'll edit to fix.