Npgsql C# multi row/value insert

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
}

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

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.