PostgreSQL Bulk Insert with C#

In my last post I have introduced PostgreSQLCopyHelper, which is a small library to wrap the Npgsql implementation of the PostgreSQL Copy command behind a nice fluent API.

From the good Npgsql documentation:

PostgreSQL has a feature allowing efficient bulk import or export of data to and from a table. This is usually a much faster way of getting data in and out of a table than using INSERT and SELECT. See documentation for the COPY command for more details.

PostgreSQLCopyHelper is released with under terms of the MIT License:

It can be installed with NuGet with the following command in the Package Manager Console:

PM> Install-Package PostgreSQLCopyHelper

Basic Usage

Imagine we have the following table we want to copy data to:

CREATE TABLE sample.unit_test
(
    col_smallint smallint,
    col_integer integer,
    col_money money,
    col_bigint bigint,
    col_timestamp timestamp,
    col_real real,
    col_double double precision,
    col_bytea bytea,
    col_uuid uuid,
    col_numeric numeric,
    col_inet inet,
    col_macaddr macaddr,
    col_date date,
    col_interval interval
);

The corresponding domain model in our application could look like this:

private class TestEntity
{
    public Int16? SmallInt { get; set; }
    public Int32? Integer { get; set; }
    public Int64? BigInt { get; set; }
    public Decimal? Money { get; set; }
    public DateTime? Timestamp { get; set; }
    public Decimal? Numeric { get; set; }
    public Single? Real { get; set; }
    public Double? DoublePrecision { get; set; }
    public byte[] ByteArray { get; set; }
    public Guid? UUID { get; set; }
    public IPAddress IpAddress { get; set; }
    public PhysicalAddress MacAddress { get; set; }
    public DateTime? Date { get; set; }
    public TimeSpan? TimeSpan { get; set; }
}

The PostgreSQLCopyHelper now defines the mapping between domain model and the database table:

var copyHelper = new PostgreSQLCopyHelper<TestEntity>()
    .WithTableName("sample", "unit_test")
    .MapSmallInt("col_smallint", x => x.SmallInt)
    .MapInteger("col_integer", x => x.Integer)
    .MapMoney("col_money", x => x.Money)
    .MapBigInt("col_bigint", x => x.BigInt)
    .MapTimeStamp("col_timestamp", x => x.Timestamp)
    .MapReal("col_real", x => x.Real)
    .MapDouble("col_double", x => x.DoublePrecision)
    .MapByteArray("col_bytea", x => x.ByteArray)
    .MapUUID("col_uuid", x => x.UUID)
    .MapInetAddress("col_inet", x => x.IpAddress)
    .MapMacAddress("col_macaddr", x => x.MacAddress)
    .MapDate("col_date", x => x.Date)
    .MapInterval("col_interval", x => x.TimeSpan)
    .MapNumeric("col_numeric", x => x.Numeric);

And then we can use it to efficiently store the data:

private void WriteToDatabase(PostgreSQLCopyHelper<TestEntity> copyHelper, IEnumerable<TestEntity> entities)
{
    using (var connection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=sampledb;User Id=philipp;Password=test_pwd;"))
    {
        connection.Open();

        copyHelper.SaveAll(connection, entities);
    }
}