Npgsql C# multi row/value insert

Question

I've been looking at the PostGres multi row/value insert which looks something like this in pure SQL:

insert into table (col1, col2, col3) values (1,2,3), (4,5,6)....

The reason I wan to use this is I have a lot of data to insert that is arriving via a queue, which I'm batching into 500/1000 record inserts at a time to improve performance.

However, I have been unable to find an example of doing this from within C#, everything I can find is adding only a single records parameter at a time, then executing, which is too slow.

I have this working using Dapper currently, but I need to expand the SQL to an upsert (insert on conflict update) which everything I have found indicated Dapper can't handle. I have found evidence the Postgres can handle upsert and multi valued in a single action.

Tom

Nina · Answer 1 · 2020-04-18T15:38:17.250

2

I didn't get your question completely right. But for bulk insert in Postgresql, this is a good answer

It gives an example for inserting multiple records from a list (RecordList) into table (user_data.part_list) :

using (var writer = conn.BeginBinaryImport(
"copy user_data.part_list from STDIN (FORMAT BINARY)"))
{
    foreach (var record in RecordList)
    {
        writer.StartRow();
        writer.Write(record.UserId);
        writer.Write(record.Age, NpgsqlTypes.NpgsqlDbType.Integer);
        writer.Write(record.HireDate, NpgsqlTypes.NpgsqlDbType.Date);
    }

    writer.Complete();
}

edited Apr 18 '20 at 15:38

answered Apr 18 '20 at 14:49

Nina

558
2
20

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/25894961) – Suraj Kumar Apr 18 '20 at 15:21
1

@SurajKumar : Okay I added the code example to the answer – Nina Apr 18 '20 at 15:38

score 1 · Answer 2 · answered Nov 04 '20 at 08:19

COPY is the fastest way but does not work if you want to do UPSERTS with an ON CONFLICT ... clause.

If it's necessary to use INSERT, ingesting n rows (with possibly varying n per invocation) can be elegantly done using UNNEST like

INSERT INTO table (col1, col2, ..., coln) SELECT UNNEST(@p1), UNNEST(@p2), ... UNNEST(@pn);

The parameters p then need to be an array of the matching type. Here's an example for an array of ints:

new NpgsqlParameter()
{
 ParameterName = "p1",
 Value = new int[]{1,2,3},
 NpgsqlDbType = NpgsqlDbType.Array | NpgsqlDbType.Integer
}

score 0 · Answer 3 · answered Jun 20 '19 at 11:44

If you want to insert many records efficiently, you probably want to take a look at Npgsql's bulk copy API, which doesn't use SQL and is the most efficient option available.

Otherwise, there's nothing special about inserting two rows rather than one:

insert into table (col1, col2, col3) values (@p1_1,@p1_2,@p1_3), (@p2_1,@p2_2,@p_3)....

Simply add the parameters with the correct name and execute just as you would any other SQL.

Npgsql C# multi row/value insert

3 Answers3