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

No fields configured for Bug.

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions