Unnest and bulk insert in PostgreSQL using .NET Npgsql

Question

I am trying to make bulk insert with single command. It works for simple values like int/string, but it failed me when inserting to jsonb column.

My example of table:

CREATE TABLE data_table2 (id INTEGER NOT NULL, name TEXT NOT NULL, test jsonb, bar jsonb)

My query from .NET (that works perfectly fine when used without @c parameter):

"INSERT INTO data_table2 (id, name, test, bar) SELECT * FROM unnest(@a, @b, @c, @d)"

I am creating @a param like this: new NpgsqlParameter<int[]>(name, value) which results in inserting row by row (just like I want). Now when I added next @c jsonb param it fails, and instead of inserting row by row I get an error 42883: function pg_catalog.unnest(jsonb) does not exist which is a lie, cause I could insert it before for only id/string. Third parameter in .net looks like this:

new NpgsqlParameter<string[]>(name, NpgsqlTypes.NpgsqlDbType.Jsonb) { Value = value.ToJson() }

and ToJson method is doing:

value.Select(x => Newtonsoft.Json.JsonConvert.SerializeObject(x, Newtonsoft.Json.Formatting.None, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })).ToArray()

I have tried changing query to:

"INSERT INTO data_table2 (id, name, test) SELECT * FROM unnest(@a, @b, ARRAY[@c])" it worked, but first two columns where inserted without a problem, but third one only had 1 row inserted with all the results in it and rest of the rows were null.

Could someone take a look what I am missing?

exyi · Accepted Answer · 2022-10-13 08:54:15Z

0

The problem is that you are not passing in array of JSONB documents, you'll have to change the argument to

new NpgsqlParameter<string[]>(name, NpgsqlDbType.Jsonb | NpgsqlDbType.Array) { Value = value.ToJson() }`

//                                  ^^^ this is `jsonb[]` type

If it still causes the same error, make sure that ToJson actually returns array.

Another option is to send the JSONs as an array of strings and the convert it in the INSERT statement:

INSERT INTO data_table2 (id, name, test, bar) SELECT a, b, c::jsonb, d::jsonb FROM unnest(@a, @b, @c, @d) foo(a, b, c, d)

answered Oct 13, 2022 at 8:54

exyi

5193 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Unnest and bulk insert in PostgreSQL using .NET Npgsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related