How to use NpgSql to insert an array of Strings into a Postgresql column

Question

I am trying to insert an array of strings into a column using .NET. It is a legacy app in VB.NET but I can convert from C# easily. Here is what I tried:

        p = New NpgsqlParameter("company_address", NpgsqlTypes.NpgsqlDbType.Array + NpgsqlTypes.NpgsqlDbType.Text)
        p.Value = data.company_address     ' <--- array of strings
        cmd.Parameters.Add(p)

I either get an error "Unable to cast object of type 'System.String[]' to type 'System.IConvertible'" with the above, or I tried using this format for the value: '{"string1","string2","string3"}' but then I get an error "Input string was not in the proper format". I got that same error when I also tried: ARRAY['string1','string2','string3']

So do arrays always have to be converted to a string of some sort of format or can NpgSqlParameter somehow be used to accept a string array?

The field company_address is defined as String() The Npgsql version is 2.2

Thanks

Answer 1

You need to do NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text (bitwise or), not NpgsqlTypes.NpgsqlDbType.Array + NpgsqlTypes.NpgsqlDbType.Text.

Another option is to omit NpgsqlDbType entirely and let Npgsql infer - when it sees array of strings it will automatically send the right type.