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

How to contribute

One of the easiest ways to contribute is to participate in discussions. You can also contribute by submitting pull requests.

General feedback and discussions?

Do you have questions or feedback on this article? Please create an issue on the GitHub issue tracker.

Something is wrong or missing?

There may be something wrong or missing in this article. If you want to help fixing it, then please make a Pull Request to this file on GitHub.