-
Notifications
You must be signed in to change notification settings - Fork 875
Milestone
Description
The issue
Target column type: timestamp with timezone NULL
I'm trying to write to target column from array of nullable DateTime (kind UTC) and get an error
Message:
System.Exception : While trying to write an array, one of its elements failed validation. You may be trying to mix types in a non-generic IList, or to write a jagged array.
---- System.InvalidCastException : Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
Tests
[Fact]
public void TestPostgresqlNullableArrayDateTimeParametersWithNull()
{
_connection.Open();
using var transaction = _connection.BeginTransaction();
_connection.Execute("create table test (dt timestamp with time zone NOT NULL);");
Action result = () => _connection.Execute("insert into test select * from unnest(@dt);",
new {dt = new DateTime?[] {DateTime.UtcNow, DateTime.UtcNow, null}});
result.Should().Throw<Exception>()
.WithMessage("While trying to write an array, one of its elements failed validation. You may be trying to mix types in a non-generic IList, or to write a jagged array.");
transaction.Rollback();
}
[Fact]
public void TestPostgresqlNullableArrayDateTimeParameters()
{
_connection.Open();
using var transaction = _connection.BeginTransaction();
_connection.Execute("create table test (dt timestamp with time zone NULL);");
Action result = () => _connection.Execute("insert into test select * from unnest(@dt);",
new { dt = new DateTime?[] { DateTime.UtcNow, DateTime.UtcNow, DateTime.UtcNow } });
result.Should().Throw<Exception>()
.WithMessage("While trying to write an array, one of its elements failed validation. You may be trying to mix types in a non-generic IList, or to write a jagged array.");
transaction.Rollback();
}
[Fact]
public void TestPostgresqlArrayDateTimeParameters()
{
_connection.Open();
using var transaction = _connection.BeginTransaction();
_connection.Execute("create table test (dt timestamp with time zone NULL);");
_connection.Execute("insert into test select * from unnest(@dt);",
new { dt = new DateTime[] { DateTime.UtcNow, DateTime.UtcNow, DateTime.UtcNow} });
var req = _connection.Query<DateTime?>("select * from test");
req.Count().Should().Be(3);
transaction.Rollback();
}Further technical details
Npgsql version: 6.0.3
PostgreSQL version: 13.0
Operating system: Windows 10
Reactions are currently unavailable
Metadata
Metadata
Assignees
Labels
No labels