What is the correct syntax to pass a null value for a timestamp column using Npgsql?

Question

I have an AWS Redshift table with a timestamp type column.

create table if not exists testtimestampfields
(
    testid varchar(50) not null,
    creationdate timestamp null
);

Since the creationdate field permits null, I thought I could pass a null DateTime as the parameter value when inserting records.

[Fact]
public async Task Can_insert_null_timestamp_field()
{
    // Custom class that calls GetClusterCredentials and returns a connection with the user and password...
    await using var conn = await _redshiftConnectionManager.GetConnectionAsync();

    await conn.OpenAsync();

    await using var cmd = new NpgsqlCommand(
        @"insert into testtimestampfields(testid, creationdate) values (@testid, @creationdate);", conn);

    var testidParam = cmd.Parameters.Add("@testid", NpgsqlDbType.Varchar);
    var creationDateParam = cmd.Parameters.Add("@creationdate", NpgsqlDbType.Timestamp);

    testidParam.Value = Guid.NewGuid().ToString();
    creationDateParam.Value = null;
    
    await cmd.ExecuteNonQueryAsync();
}

But when I try this await cmd.ExecuteNonQueryAsync(); fails with:

System.InvalidCastException: Parameter @creationdate must be set

System.InvalidCastException
Parameter @creationdate must be set
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlParameterCollection.ValidateAndBind(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   ...

What is the correct syntax to pass a null value for a timestamp column using Npgsql?

Answer 1

Changing null to DBNull.Value works.

creationDateParam.Value = DBNull.Value;

Using a .NET DateTime? variable to set the parameter value conditionally:

creationDateParam.Value = dateTime.HasValue ? dateTime.Value : DBNull.Value;