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

Answer 1

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
}

Answer 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();
}