Passing a string array to Redshift

0

I have a List<string> in C# which I would like to use in a query to Redshift. For example:

List<string> fieldStringList = new List<string>(){"field1","field2","field3"};
await using var conn = new NpgsqlConnection("My Connection String");
await conn.OpenAsync();    
await using var cmd = new NpgsqlCommand("SELECT TOP 10 * FROM example WHERE field IN (:field);", conn);
cmd.Parameters.AddWithValue("field", NpgsqlDbType.Array | NpgsqlDbType.Varchar, fieldStringList.ToArray());

This fails with the exception

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: character varying = character varying[]

It looks like I am trying to compare a varchar to a varchar[]?

How should I pass the array to the Redshift query?

c#
amazon-redshift
npgsql
asked on Stack Overflow Jun 2, 2020 by infojolt • edited Jun 2, 2020 by infojolt

1 Answer

0

The IN operator doesn't accept arrays. Try the following:

SELECT TOP 10 * FROM example WHERE field = ANY (:field);
answered on Stack Overflow Jun 3, 2020 by Shay Rojansky

User contributions licensed under CC BY-SA 3.0