Skip to content

Error occured when writing to timestamptz from DateTime?[] #4340

@LegaNoga

Description

@LegaNoga

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

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions