08P01: insufficient data left in message for Nullable DateTime

Question

I have database table having one column defined as timestamp without time zone. Now from my c# application, when I try to insert null value in that column using NpgSql BeginBinaryImport it gives error message as mentioned below:

08P01: insufficient data left in message

Below is the code which I am trying to execute:

static void Main(string[] args)
{
    BulkInsert();
}

private static void BulkInsert()
{

    DataTable table = new DataTable();
    table.Columns.Add("firstname", typeof(String));
    table.Columns.Add("lastname", typeof(String));
    table.Columns.Add("logdatetime", typeof(DateTime));
    table.Columns.Add("status", typeof(int));
    table.Columns.Add("id", typeof(long));

    var dataRow = table.NewRow();
    dataRow["firstname"] = "MyFirstName";
    dataRow["lastname"] = "MyLastName";
    dataRow["logdatetime"] = DBNull.Value;
    dataRow["status"] = 1;
    dataRow["id"] = 10;
    table.Rows.Add(dataRow);

    var data = new DataAccess();

    using (var npgsqlConn = new NpgsqlConnection([ConnectionString]))
    {
        npgsqlConn.Open();
        var commandFormat = string.Format(CultureInfo.InvariantCulture, "COPY {0} {1} FROM STDIN BINARY", "logging.testtable", "(firstName,LastName,logdatetime,status,id)");
        using (var writer = npgsqlConn.BeginBinaryImport(commandFormat))
        {
            foreach (DataRow item in dataTable.Rows)
            {
                writer.StartRow();
                foreach (var item1 in item.ItemArray)
                {
                     writer.Write(item1);
                }

            }
        }

        npgsqlConn.Close();
}

Answer 1

The issue is the DBNull.Value you're trying to write - Npgsql doesn't support writing nulls this way. To write a null you need to use the WriteNull() method instead.

I can make Npgsql accept DBNull.Value, but only for the overload of Write() which also accepts an NpgsqlDbType (because Npgsql has to write the data type, and with DBNull.Value we have no idea what that is).

EDIT: Have done this, see https://github.com/npgsql/npgsql/issues/1122.