MigrationsNpgsqlTest.cs

#nullable enable

using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata.Internal;
using Npgsql.EntityFrameworkCore.PostgreSQL.Scaffolding.Internal;
using Npgsql.EntityFrameworkCore.PostgreSQL.TestUtilities;

namespace Npgsql.EntityFrameworkCore.PostgreSQL.Migrations;

public class MigrationsNpgsqlTest : MigrationsTestBase<MigrationsNpgsqlTest.MigrationsNpgsqlFixture>
{
    public MigrationsNpgsqlTest(MigrationsNpgsqlFixture fixture, ITestOutputHelper testOutputHelper)
        : base(fixture)
    {
        Fixture.TestSqlLoggerFactory.Clear();
        Fixture.TestSqlLoggerFactory.SetTestOutputHelper(testOutputHelper);
    }

    #region Table

    public override async Task Create_table()
    {
        await base.Create_table();

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public override async Task Create_table_all_settings()
    {
        await base.Create_table_all_settings();

        AssertSql(
            """
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'dbo2') THEN
        CREATE SCHEMA dbo2;
    END IF;
END $EF$;
""",
            //
            """
CREATE TABLE dbo2."People" (
    "CustomId" integer GENERATED BY DEFAULT AS IDENTITY,
    "EmployerId" integer NOT NULL,
    "SSN" character varying(11) COLLATE "POSIX" NOT NULL,
    CONSTRAINT "PK_People" PRIMARY KEY ("CustomId"),
    CONSTRAINT "AK_People_SSN" UNIQUE ("SSN"),
    CONSTRAINT "CK_People_EmployerId" CHECK ("EmployerId" > 0),
    CONSTRAINT "FK_People_Employers_EmployerId" FOREIGN KEY ("EmployerId") REFERENCES "Employers" ("Id") ON DELETE CASCADE
);
COMMENT ON TABLE dbo2."People" IS 'Table comment';
COMMENT ON COLUMN dbo2."People"."EmployerId" IS 'Employer ID comment';
""",
            //
            """CREATE INDEX "IX_People_EmployerId" ON dbo2."People" ("EmployerId");""");
    }

    public override async Task Create_table_no_key()
    {
        await base.Create_table_no_key();

        AssertSql(
            """
CREATE TABLE "Anonymous" (
    "SomeColumn" integer NOT NULL
);
""");
    }

    public override async Task Create_table_with_comments()
    {
        await base.Create_table_with_comments();

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
COMMENT ON TABLE "People" IS 'Table comment';
COMMENT ON COLUMN "People"."Name" IS 'Column comment';
""");
    }

    public override async Task Create_table_with_multiline_comments()
    {
        await base.Create_table_with_multiline_comments();

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
COMMENT ON TABLE "People" IS 'This is a multi-line
table comment.
More information can
be found in the docs.';
COMMENT ON COLUMN "People"."Name" IS 'This is a multi-line
column comment.
More information can
be found in the docs.';
""");
    }

    public override async Task Create_table_with_computed_column(bool? stored)
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Create_table_with_computed_column(stored));
            return;
        }

        if (stored != true)
        {
            // Non-stored generated columns aren't yet supported (PG12)
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Create_table_with_computed_column(stored));
            return;
        }

        await base.Create_table_with_computed_column(stored: true);

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Sum" text GENERATED ALWAYS AS ("X" + "Y") STORED,
    "X" integer NOT NULL,
    "Y" integer NOT NULL,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_identity_by_default()
    {
        await Test(
            _ => { },
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityByDefaultColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_identity_always()
    {
        await Test(
            _ => { },
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityAlwaysColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityAlwaysColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED ALWAYS AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_identity_always_with_options()
    {
        await Test(
            _ => { },
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityAlwaysColumn()
                .HasIdentityOptions(startValue: 10, incrementBy: 2, maxValue: 2000),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityAlwaysColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                var options = IdentitySequenceOptionsData.Get(column);
                Assert.Equal(10, options.StartValue);
                Assert.Equal(2, options.IncrementBy);
                Assert.Equal(2000, options.MaxValue);
                Assert.Null(options.MinValue);
                Assert.Equal(1, options.NumbersToCache);
                Assert.False(options.IsCyclic);
            });

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 2 MAXVALUE 2000),
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_serial()
    {
        await Test(
            _ => { },
            builder => builder.Entity("People").Property<int>("Id")
                .UseSerialColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.SerialColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);

                Assert.Empty(model.Sequences);
            });

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" serial NOT NULL,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_system_column()
    {
        // System columns (e.g. xmin) are implicitly always present. If an xmin property is present,
        // nothing should happen.
        await Test(
            _ => { },
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<uint>("xmin");
                    e.HasKey("Id");
                }),
            model =>
            {
                var table = Assert.Single(model.Tables);
                Assert.Equal("Id", Assert.Single(table.Columns).Name);
            });

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_oid_column()
    {
        var isPgAtLeast12 = TestEnvironment.PostgresVersion.AtLeast(12);

        await Test(
            _ => { },
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<uint>("oid");
                    e.HasKey("Id");
                }),
            model =>
            {
                var table = Assert.Single(model.Tables);
                Assert.Equal("Id", isPgAtLeast12 ? table.Columns[0].Name : Assert.Single(table.Columns).Name);

                if (isPgAtLeast12)
                {
                    Assert.Equal("oid", table.Columns[1].Name);
                }
            });

        AssertSql(
            isPgAtLeast12
                ? """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    oid bigint NOT NULL,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
"""
                : """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_storage_parameter()
    {
        await Test(
            _ => { },
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                    e.HasStorageParameter("fillfactor", 70);
                    e.HasStorageParameter("user_catalog_table", true);
                }),
            model =>
            {
                var table = Assert.Single(model.Tables);

                Assert.Collection(
                    table.GetAnnotations()
                        .Where(a => a.Name.StartsWith(NpgsqlAnnotationNames.StorageParameterPrefix, StringComparison.Ordinal))
                        .OrderBy(a => a.Name),
                    annotation =>
                    {
                        Assert.Equal(NpgsqlAnnotationNames.StorageParameterPrefix + "fillfactor", annotation.Name);
                        // Storage parameter values always get scaffolded as strings (PG storage is simply 'name=value')
                        Assert.Equal("70", annotation.Value);
                    },
                    annotation =>
                    {
                        Assert.Equal(NpgsqlAnnotationNames.StorageParameterPrefix + "user_catalog_table", annotation.Name);
                        // Storage parameter values always get scaffolded as strings (PG storage is simply 'name=value')
                        Assert.Equal("true", annotation.Value);
                    });
            });

        AssertSql(
            """
CREATE TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
)
WITH (fillfactor=70, user_catalog_table=true);
""");
    }

    [Fact]
    public virtual async Task Create_table_with_unlogged()
    {
        await Test(
            _ => { },
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                    e.IsUnlogged();
                }),
            asserter: null); // We don't scaffold unlogged

        AssertSql(
            """
CREATE UNLOGGED TABLE "People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    public override async Task Drop_table()
    {
        await base.Drop_table();

        AssertSql("""DROP TABLE "People";""");
    }

    public override async Task Alter_table_add_comment()
    {
        await base.Alter_table_add_comment();

        AssertSql("""COMMENT ON TABLE "People" IS 'Table comment';""");
    }

    public override async Task Alter_table_add_comment_non_default_schema()
    {
        await base.Alter_table_add_comment_non_default_schema();

        AssertSql("""COMMENT ON TABLE "SomeOtherSchema"."People" IS 'Table comment';""");
    }

    public override async Task Alter_table_change_comment()
    {
        await base.Alter_table_change_comment();

        AssertSql("""COMMENT ON TABLE "People" IS 'Table comment2';""");
    }

    public override async Task Alter_table_remove_comment()
    {
        await base.Alter_table_remove_comment();

        AssertSql("""COMMENT ON TABLE "People" IS NULL;""");
    }

    [Fact]
    public virtual async Task Alter_table_change_storage_parameters()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            builder => builder.Entity(
                "People", e =>
                {
                    e.HasStorageParameter("fillfactor", 70);
                    e.HasStorageParameter("user_catalog_table", true);
                    e.HasStorageParameter("parallel_workers", 8);
                }),
            builder => builder.Entity(
                "People", e =>
                {
                    // Add parameter
                    e.HasStorageParameter("autovacuum_enabled", true);
                    // Change parameter
                    e.HasStorageParameter("fillfactor", 80);
                    // Drop parameter user_catalog
                    // Leave parameter unchanged
                    e.HasStorageParameter("parallel_workers", 8);
                }),
            asserter: null); // We don't scaffold storage parameters

        AssertSql(
            """
ALTER TABLE "People" SET (autovacuum_enabled=true, fillfactor=80);
ALTER TABLE "People" RESET (user_catalog_table);
""");
    }

    [Fact]
    public virtual async Task Alter_table_make_unlogged()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").IsUnlogged(),
            asserter: null); // We don't scaffold unlogged

        AssertSql("""ALTER TABLE "People" SET UNLOGGED;""");
    }

    [Fact]
    public virtual async Task Alter_table_make_logged()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            builder => builder.Entity("People").IsUnlogged(),
            _ => { },
            asserter: null); // We don't scaffold unlogged

        AssertSql("""ALTER TABLE "People" SET LOGGED;""");
    }

    public override async Task Rename_table()
    {
        await base.Rename_table();

        AssertSql(
            """ALTER TABLE "People" DROP CONSTRAINT "PK_People";""",
            //
            """ALTER TABLE "People" RENAME TO "Persons";""",
            //
            """ALTER TABLE "Persons" ADD CONSTRAINT "PK_Persons" PRIMARY KEY ("Id");""");
    }

    public override async Task Rename_table_with_primary_key()
    {
        await base.Rename_table_with_primary_key();

        AssertSql(
            """ALTER TABLE "People" DROP CONSTRAINT "PK_People";""",
            //
            """ALTER TABLE "People" RENAME TO "Persons";""",
            //
            """ALTER TABLE "Persons" ADD CONSTRAINT "PK_Persons" PRIMARY KEY ("Id");""");
    }

    public override async Task Move_table()
    {
        await base.Move_table();

        AssertSql(
            """
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'TestTableSchema') THEN
        CREATE SCHEMA "TestTableSchema";
    END IF;
END $EF$;
""",
            //
            """ALTER TABLE "TestTable" SET SCHEMA "TestTableSchema";""");
    }

    #endregion

    #region Schema

    public override async Task Create_schema()
    {
        await base.Create_schema();

        AssertSql(
            """
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'SomeOtherSchema') THEN
        CREATE SCHEMA "SomeOtherSchema";
    END IF;
END $EF$;
""",
            //
            """
CREATE TABLE "SomeOtherSchema"."People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    [Fact]
    public virtual async Task Create_schema_public_is_ignored()
    {
        await Test(
            _ => { },
            builder => builder.Entity("People")
                .ToTable("People", "public")
                .Property<int>("Id"),
            model => Assert.Equal("public", Assert.Single(model.Tables).Schema));

        AssertSql(
            """
CREATE TABLE public."People" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    CONSTRAINT "PK_People" PRIMARY KEY ("Id")
);
""");
    }

    #endregion

    #region Column

    public override async Task Add_column_with_defaultValue_string()
    {
        await base.Add_column_with_defaultValue_string();

        AssertSql("""ALTER TABLE "People" ADD "Name" text NOT NULL DEFAULT 'John Doe';""");
    }

    public override async Task Add_column_with_defaultValue_datetime()
    {
        // We default to mapping DateTime to 'timestamp with time zone', so we need to explicitly specify UTC
        await Test(
            builder => builder.Entity("People").Property<int>("Id"),
            _ => { },
            builder => builder.Entity("People").Property<DateTime>("Birthday")
                .HasDefaultValue(new DateTime(2015, 4, 12, 17, 5, 0, DateTimeKind.Utc)),
            model =>
            {
                var table = Assert.Single(model.Tables);
                Assert.Equal(2, table.Columns.Count);
                var birthdayColumn = Assert.Single(table.Columns, c => c.Name == "Birthday");
                Assert.False(birthdayColumn.IsNullable);
            });

        AssertSql("""ALTER TABLE "People" ADD "Birthday" timestamp with time zone NOT NULL DEFAULT TIMESTAMPTZ '2015-04-12T17:05:00Z';""");
    }

    [Fact]
    public override async Task Add_column_with_defaultValueSql()
    {
        await base.Add_column_with_defaultValueSql();

        AssertSql("""ALTER TABLE "People" ADD "Sum" integer NOT NULL DEFAULT (1 + 2);""");
    }

    public override async Task Add_column_with_computedSql(bool? stored)
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Add_column_with_computedSql(stored));
            return;
        }

        if (stored != true)
        {
            // Non-stored generated columns aren't yet supported (PG12)
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Add_column_with_computedSql(stored));
            return;
        }

        await base.Add_column_with_computedSql(stored: true);

        AssertSql("""ALTER TABLE "People" ADD "Sum" text GENERATED ALWAYS AS ("X" + "Y") STORED;""");
    }

    public override async Task Add_column_with_required()
    {
        await base.Add_column_with_required();

        AssertSql("""ALTER TABLE "People" ADD "Name" text NOT NULL DEFAULT '';""");
    }

    public override async Task Add_column_with_ansi()
    {
        await base.Add_column_with_ansi();

        AssertSql("""ALTER TABLE "People" ADD "Name" text;""");
    }

    public override async Task Add_column_with_max_length()
    {
        await base.Add_column_with_max_length();

        AssertSql("""ALTER TABLE "People" ADD "Name" character varying(30);""");
    }

    public override async Task Add_column_with_unbounded_max_length()
    {
        await base.Add_column_with_unbounded_max_length();

        AssertSql("""ALTER TABLE "People" ADD "Name" text;""");
    }

    public override async Task Add_column_with_max_length_on_derived()
    {
        await base.Add_column_with_max_length_on_derived();

        AssertSql();
    }

    public override async Task Add_column_with_fixed_length()
    {
        await base.Add_column_with_fixed_length();

        AssertSql("""ALTER TABLE "People" ADD "Name" character(100);""");
    }

    public override async Task Add_column_with_comment()
    {
        await base.Add_column_with_comment();

        AssertSql(
            """
ALTER TABLE "People" ADD "FullName" text;
COMMENT ON COLUMN "People"."FullName" IS 'My comment';
""");
    }

    public override async Task Add_column_with_collation()
    {
        await base.Add_column_with_collation();

        AssertSql("""ALTER TABLE "People" ADD "Name" text COLLATE "POSIX";""");
    }

    public override async Task Add_column_computed_with_collation(bool stored)
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Add_column_computed_with_collation(stored));
            return;
        }

        if (stored != true)
        {
            // Non-stored generated columns aren't yet supported (PG12)
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Create_table_with_computed_column(stored));
            return;
        }

        await base.Add_column_computed_with_collation(stored);

        AssertSql("""ALTER TABLE "People" ADD "Name" text COLLATE "POSIX" GENERATED ALWAYS AS ('hello') STORED;""");
    }

    public override async Task Add_column_shared()
    {
        await base.Add_column_shared();

        AssertSql();
    }

    [Fact]
    public virtual async Task Add_column_with_upper_case_store_type()
    {
        // At least for now, it's the user's responsibility to quote store type name when needed,
        // because it seems standard for people to specify either text or TEXT, and both should work.
        await Test(
            _ => { },
            builder => builder.Entity("People").Property<string>("Name").HasColumnType("TEXT"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "Name");
                Assert.Equal("text", column.StoreType);
            });

        AssertSql(
            """
CREATE TABLE "People" (
    "Name" TEXT
);
""");
    }

    [Fact]
    public virtual async Task Add_column_with_identity_by_default()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int?>("SomeColumn")
                .UseIdentityByDefaultColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SomeColumn");
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql("""ALTER TABLE "People" ADD "SomeColumn" integer GENERATED BY DEFAULT AS IDENTITY;""");
    }

    [Fact]
    public virtual async Task Add_column_with_identity_always()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int?>("SomeColumn")
                .UseIdentityAlwaysColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SomeColumn");
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityAlwaysColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql("""ALTER TABLE "People" ADD "SomeColumn" integer GENERATED ALWAYS AS IDENTITY;""");
    }

    [Fact]
    public virtual async Task Add_column_with_identity_by_default_with_all_options()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int?>("SomeColumn")
                .UseIdentityByDefaultColumn()
                .HasIdentityOptions(
                    startValue: 5,
                    incrementBy: 2,
                    minValue: 3,
                    maxValue: 2000,
                    cyclic: true,
                    numbersToCache: 10),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SomeColumn");
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                var options = IdentitySequenceOptionsData.Get(column);
                Assert.Equal(5, options.StartValue);
                Assert.Equal(2, options.IncrementBy);
                Assert.Equal(3, options.MinValue);
                Assert.Equal(2000, options.MaxValue);
                Assert.True(options.IsCyclic);
                Assert.Equal(10, options.NumbersToCache);
            });

        AssertSql(
            """
ALTER TABLE "People" ADD "SomeColumn" integer GENERATED BY DEFAULT AS IDENTITY (START WITH 5 INCREMENT BY 2 MINVALUE 3 MAXVALUE 2000 CYCLE CACHE 10);
""");
    }

    [Fact]
    public virtual Task Add_column_optional_with_serial_not_supported()
        => TestThrows<NotSupportedException>(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int?>("SomeColumn")
                .UseSerialColumn());

    [Fact]
    public virtual async Task Add_column_required_with_serial()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int>("SomeColumn")
                .UseSerialColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SomeColumn");
                Assert.Equal(NpgsqlValueGenerationStrategy.SerialColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql("""ALTER TABLE "People" ADD "SomeColumn" serial NOT NULL;""");
    }

    [Fact]
    public virtual async Task Add_column_required_with_identity_by_default()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int>("SomeColumn")
                .UseIdentityByDefaultColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SomeColumn");
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql("""ALTER TABLE "People" ADD "SomeColumn" integer GENERATED BY DEFAULT AS IDENTITY;""");
    }

    [Fact]
    public virtual async Task Add_column_system()
    {
        // System columns (e.g. xmin) are implicitly always present. If an xmin property is added,
        // nothing should happen.
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<uint>("xmin"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                Assert.Equal("Id", Assert.Single(table.Columns).Name);
            });

        AssertSql();
    }

    [Fact]
    public virtual async Task Add_column_with_huge_varchar()
    {
        // PostgreSQL doesn't allow varchar(x) with x > 10485760, so we map this to text.
        // See #342 and https://www.postgresql.org/message-id/15790.1291824247%40sss.pgh.pa.us
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<string>("Name").HasMaxLength(10485761),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "Name");
                Assert.Equal("text", column.StoreType);
            });

        AssertSql("""ALTER TABLE "People" ADD "Name" text;""");
    }

    [Fact]
    public virtual async Task Add_column_with_compression_method()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(14))
        {
            return;
        }

        await Test(
            builder => builder.Entity("Blogs", e => e.Property<int>("Id")),
            _ => { },
            builder => builder.Entity("Blogs").Property<string>("Title").UseCompressionMethod("pglz"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "Title");
                Assert.Equal("pglz", column[NpgsqlAnnotationNames.CompressionMethod]);
            });

        AssertSql("""ALTER TABLE "Blogs" ADD "Title" text COMPRESSION pglz;""");
    }

    public override async Task Alter_column_change_type()
    {
        await base.Alter_column_change_type();

        AssertSql("""ALTER TABLE "People" ALTER COLUMN "SomeColumn" TYPE bigint;""");
    }

    public override async Task Alter_column_make_required()
    {
        await base.Alter_column_make_required();

        AssertSql(
            """
UPDATE "People" SET "SomeColumn" = '' WHERE "SomeColumn" IS NULL;
ALTER TABLE "People" ALTER COLUMN "SomeColumn" SET NOT NULL;
ALTER TABLE "People" ALTER COLUMN "SomeColumn" SET DEFAULT '';
""");
    }

    public override async Task Alter_column_make_required_with_null_data()
    {
        await base.Alter_column_make_required_with_null_data();

        AssertSql(
            """
UPDATE "People" SET "SomeColumn" = '' WHERE "SomeColumn" IS NULL;
ALTER TABLE "People" ALTER COLUMN "SomeColumn" SET NOT NULL;
ALTER TABLE "People" ALTER COLUMN "SomeColumn" SET DEFAULT '';
""");
    }

    public override async Task Alter_column_make_required_with_index()
    {
        await base.Alter_column_make_required_with_index();

        AssertSql(
            """
UPDATE "People" SET "SomeColumn" = '' WHERE "SomeColumn" IS NULL;
ALTER TABLE "People" ALTER COLUMN "SomeColumn" SET NOT NULL;
ALTER TABLE "People" ALTER COLUMN "SomeColumn" SET DEFAULT '';
""");
    }

    public override async Task Alter_column_make_required_with_composite_index()
    {
        await base.Alter_column_make_required_with_composite_index();

        AssertSql(
            """
UPDATE "People" SET "FirstName" = '' WHERE "FirstName" IS NULL;
ALTER TABLE "People" ALTER COLUMN "FirstName" SET NOT NULL;
ALTER TABLE "People" ALTER COLUMN "FirstName" SET DEFAULT '';
""");
    }

    public override async Task Alter_column_make_computed(bool? stored)
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Add_column_with_computedSql(stored));
            return;
        }

        if (stored != true)
        {
            // Non-stored generated columns aren't yet supported (PG12)
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Add_column_with_computedSql(stored));
            return;
        }

        await base.Alter_column_make_computed(stored);

        AssertSql(
            """ALTER TABLE "People" DROP COLUMN "Sum";""",
            //
            """ALTER TABLE "People" ADD "Sum" integer GENERATED ALWAYS AS ("X" + "Y") STORED NOT NULL;""");
    }

    public override async Task Alter_column_change_computed()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Alter_column_change_computed());
            return;
        }

        // Non-stored generated columns aren't yet supported (PG12), so we override to used stored
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("X");
                    e.Property<int>("Y");
                    e.Property<int>("Sum");
                }),
            builder => builder.Entity("People").Property<int>("Sum")
                .HasComputedColumnSql($"{DelimitIdentifier("X")} + {DelimitIdentifier("Y")}", stored: true),
            builder => builder.Entity("People").Property<int>("Sum")
                .HasComputedColumnSql($"{DelimitIdentifier("X")} - {DelimitIdentifier("Y")}", stored: true),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var sumColumn = Assert.Single(table.Columns, c => c.Name == "Sum");
                Assert.Contains("X", sumColumn.ComputedColumnSql);
                Assert.Contains("Y", sumColumn.ComputedColumnSql);
                Assert.Contains("-", sumColumn.ComputedColumnSql);
            });

        AssertSql(
            """ALTER TABLE "People" DROP COLUMN "Sum";""",
            //
            """ALTER TABLE "People" ADD "Sum" integer GENERATED ALWAYS AS ("X" - "Y") STORED NOT NULL;""");
    }

    public override async Task Alter_column_change_computed_recreates_indexes()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Alter_column_change_computed());
            return;
        }

        // Non-stored generated columns aren't yet supported (PG12), so we override to used stored
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("X");
                    e.Property<int>("Y");
                    e.Property<int>("Sum");

                    e.HasIndex("Sum");
                }),
            builder => builder.Entity("People").Property<int>("Sum")
                .HasComputedColumnSql($"{DelimitIdentifier("X")} + {DelimitIdentifier("Y")}", stored: true),
            builder => builder.Entity("People").Property<int>("Sum")
                .HasComputedColumnSql($"{DelimitIdentifier("X")} - {DelimitIdentifier("Y")}", stored: true),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var sumColumn = Assert.Single(table.Columns, c => c.Name == "Sum");
                if (AssertComputedColumns)
                {
                    Assert.Contains("X", sumColumn.ComputedColumnSql);
                    Assert.Contains("Y", sumColumn.ComputedColumnSql);
                    Assert.Contains("-", sumColumn.ComputedColumnSql);
                }

                var sumIndex = Assert.Single(table.Indexes);
                Assert.Collection(sumIndex.Columns, c => Assert.Equal("Sum", c.Name));
            });

        AssertSql(
            """ALTER TABLE "People" DROP COLUMN "Sum";""",
            //
            """ALTER TABLE "People" ADD "Sum" integer GENERATED ALWAYS AS ("X" - "Y") STORED NOT NULL;""",
            //
            """CREATE INDEX "IX_People_Sum" ON "People" ("Sum");""");
    }

    public override Task Alter_column_change_computed_type()
        => Assert.ThrowsAsync<NotSupportedException>(() => base.Alter_column_change_computed());

    public override async Task Alter_column_make_non_computed()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Alter_column_make_non_computed());
            return;
        }

        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("X");
                    e.Property<int>("Y");
                }),
            builder => builder.Entity("People").Property<int>("Sum")
                .HasComputedColumnSql(
                    """
                    "X" + "Y"
                    """, stored: true),
            builder => builder.Entity("People").Property<int>("Sum"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var sumColumn = Assert.Single(table.Columns, c => c.Name == "Sum");
                Assert.Null(sumColumn.ComputedColumnSql);
                Assert.NotEqual(true, sumColumn.IsStored);
            });

        AssertSql(
            """ALTER TABLE "People" DROP COLUMN "Sum";""",
            //
            """ALTER TABLE "People" ADD "Sum" integer NOT NULL;""");
    }

    public override async Task Alter_column_add_comment()
    {
        await base.Alter_column_add_comment();

        AssertSql("""COMMENT ON COLUMN "People"."Id" IS 'Some comment';""");
    }

    public override async Task Alter_computed_column_add_comment()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            await Assert.ThrowsAsync<NotSupportedException>(() => base.Alter_computed_column_add_comment());
            return;
        }

        await Test(
            builder => builder.Entity(
                "People", x =>
                {
                    x.Property<int>("Id");
                    x.Property<int>("SomeColumn").HasComputedColumnSql("42", stored: true);
                }),
            _ => { },
            builder => builder.Entity("People").Property<int>("SomeColumn").HasComment("Some comment"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SomeColumn");
                if (AssertComments)
                {
                    Assert.Equal("Some comment", column.Comment);
                }
            });

        AssertSql("""COMMENT ON COLUMN "People"."SomeColumn" IS 'Some comment';""");
    }

    public override async Task Alter_column_change_comment()
    {
        await base.Alter_column_change_comment();

        AssertSql("""COMMENT ON COLUMN "People"."Id" IS 'Some comment2';""");
    }

    public override async Task Alter_column_remove_comment()
    {
        await base.Alter_column_remove_comment();

        AssertSql(
            """COMMENT ON COLUMN "People"."Id" IS NULL;""");
    }

    [Fact]
    public virtual async Task Alter_column_make_identity_by_default()
    {
        await Test(
            builder => builder.Entity("People").Property<int>("Id")
                .ValueGeneratedNever(),
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityByDefaultColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql(
            """
ALTER TABLE "People" ALTER COLUMN "Id" DROP DEFAULT;
ALTER TABLE "People" ALTER COLUMN "Id" ADD GENERATED BY DEFAULT AS IDENTITY;
""");
    }

    [Fact]
    public virtual async Task Alter_column_make_identity_always()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityAlwaysColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityAlwaysColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql("""ALTER TABLE "People" ALTER COLUMN "Id" SET GENERATED ALWAYS;""");
    }

    [Fact]
    public virtual async Task Alter_column_make_default_into_identity()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id").HasDefaultValue(8);
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityAlwaysColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityAlwaysColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql(
            """
ALTER TABLE "People" ALTER COLUMN "Id" DROP DEFAULT;
ALTER TABLE "People" ALTER COLUMN "Id" ADD GENERATED ALWAYS AS IDENTITY;
""");
    }

    [Fact]
    public virtual async Task Alter_column_make_identity_by_default_with_options()
    {
        await Test(
            builder => builder.Entity("People").Property<int>("Id")
                .ValueGeneratedNever(),
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityByDefaultColumn()
                .HasIdentityOptions(startValue: 10, incrementBy: 2, maxValue: 2000),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                var options = IdentitySequenceOptionsData.Get(column);
                Assert.Equal(10, options.StartValue);
                Assert.Equal(2, options.IncrementBy);
                Assert.Equal(2000, options.MaxValue);
                Assert.Null(options.MinValue);
                Assert.Equal(1, options.NumbersToCache);
                Assert.False(options.IsCyclic);
            });

        AssertSql(
            """
ALTER TABLE "People" ALTER COLUMN "Id" DROP DEFAULT;
ALTER TABLE "People" ALTER COLUMN "Id" ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 2 MAXVALUE 2000);
""");
    }

    [Fact]
    public virtual async Task Alter_column_make_identity_with_default_options()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity("People").Property<int>("Id")
                .UseIdentityByDefaultColumn()
                .HasIdentityOptions(startValue: 1, incrementBy: 1, minValue: 1, maxValue: null),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                var options = IdentitySequenceOptionsData.Get(column);
                Assert.Null(options.StartValue);
                Assert.Equal(1, options.IncrementBy);
                Assert.Null(options.MaxValue);
                Assert.Null(options.MinValue);
                Assert.Equal(1, options.NumbersToCache);
                Assert.False(options.IsCyclic);
            });

        AssertSql(
            """
ALTER TABLE "People" ALTER COLUMN "Id" RESTART WITH 1;
ALTER TABLE "People" ALTER COLUMN "Id" SET MINVALUE 1;
""");
    }

    [Fact]
    public virtual async Task Alter_column_change_identity_options()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id").UseIdentityByDefaultColumn();
                    e.HasKey("Id");
                }),
            builder => builder.Entity("People").Property<int>("Id")
                .HasIdentityOptions(incrementBy: 1, maxValue: 1000, cyclic: false),
            builder => builder.Entity("People").Property<int>("Id")
                .HasIdentityOptions(incrementBy: 2, maxValue: 1000, cyclic: true),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                var options = IdentitySequenceOptionsData.Get(column);
                Assert.Equal(2, options.IncrementBy);
                Assert.Equal(1000, options.MaxValue);
                Assert.True(options.IsCyclic);
            });

        AssertSql(
            """
ALTER TABLE "People" ALTER COLUMN "Id" SET INCREMENT BY 2;
ALTER TABLE "People" ALTER COLUMN "Id" SET CYCLE;
""");
    }

    [Fact]
    public virtual async Task Alter_column_remove_identity_options()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id").UseIdentityByDefaultColumn();
                    e.HasKey("Id");
                }),
            builder => builder.Entity("People").Property<int>("Id")
                .HasIdentityOptions(startValue: 5, incrementBy: 2, cyclic: true, numbersToCache: 5),
            _ => { },
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                var options = IdentitySequenceOptionsData.Get(column);
                Assert.Equal(5, options.StartValue); // Restarting doesn't change the scaffolded start value
                Assert.Equal(1, options.IncrementBy);
                Assert.False(options.IsCyclic);
                Assert.Equal(1, options.NumbersToCache);
            });

        AssertSql(
            """
ALTER TABLE "People" ALTER COLUMN "Id" RESTART WITH 1;
ALTER TABLE "People" ALTER COLUMN "Id" SET INCREMENT BY 1;
ALTER TABLE "People" ALTER COLUMN "Id" SET NO CYCLE;
ALTER TABLE "People" ALTER COLUMN "Id" SET CACHE 1;
""");
    }

    [Fact]
    public virtual async Task Alter_column_make_serial()
    {
        await Test(
            builder => builder.Entity("People").Property<int>("Id")
                .ValueGeneratedNever(),
            builder => builder.Entity("People").Property<int>("Id")
                .UseSerialColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.SerialColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);

                Assert.Empty(model.Sequences);
            });

        AssertSql(
            """
CREATE SEQUENCE "People_Id_seq" AS integer START WITH 1 INCREMENT BY 1 NO CYCLE;
""",
            //
            """
ALTER TABLE "People" ALTER COLUMN "Id" SET DEFAULT (nextval('"People_Id_seq"'));
ALTER SEQUENCE "People_Id_seq" OWNED BY "People"."Id";
""");
    }

    [Fact]
    public virtual async Task Alter_column_make_serial_in_non_default_schema()
    {
        await Test(
            builder => builder.Entity("People", e => e.ToTable("People", "some_schema")),
            builder => builder.Entity("People").Property<int>("Id")
                .ValueGeneratedNever(),
            builder => builder.Entity("People").Property<int>("Id")
                .UseSerialColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.SerialColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);

                Assert.Empty(model.Sequences);
            });

        AssertSql(
            """
CREATE SEQUENCE some_schema."People_Id_seq" AS integer START WITH 1 INCREMENT BY 1 NO CYCLE;
""",
            //
            """
ALTER TABLE some_schema."People" ALTER COLUMN "Id" SET DEFAULT (nextval('some_schema."People_Id_seq"'));
ALTER SEQUENCE some_schema."People_Id_seq" OWNED BY some_schema."People"."Id";
""");
    }

    [Fact]
    public virtual async Task Alter_column_long_make_bigserial()
    {
        await Test(
            builder => builder.Entity("People").Property<long>("Id")
                .ValueGeneratedNever(),
            builder => builder.Entity("People").Property<long>("Id")
                .UseSerialColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.SerialColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Equal("bigint", column.StoreType);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);

                Assert.Empty(model.Sequences);
            });

        AssertSql(
            """
CREATE SEQUENCE "People_Id_seq" START WITH 1 INCREMENT BY 1 NO CYCLE;
""",
            //
            """
ALTER TABLE "People" ALTER COLUMN "Id" SET DEFAULT (nextval('"People_Id_seq"'));
ALTER SEQUENCE "People_Id_seq" OWNED BY "People"."Id";
""");
    }

    [Fact]
    public virtual async Task Alter_column_change_identity_type()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            builder => builder.Entity("People").Property<int>("Id").UseIdentityByDefaultColumn(),
            builder => builder.Entity("People").Property<int>("Id").UseIdentityAlwaysColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityAlwaysColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql(
            """ALTER TABLE "People" ALTER COLUMN "Id" SET GENERATED ALWAYS;""");
    }

    [Fact]
    public virtual async Task Alter_column_change_serial_to_identity()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            builder => builder.Entity("People").Property<int>("Id").UseSerialColumn(),
            builder => builder.Entity("People").Property<int>("Id").UseIdentityAlwaysColumn(),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityAlwaysColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql(
            """
ALTER SEQUENCE "People_Id_seq" RENAME TO "People_Id_old_seq";
ALTER TABLE "People" ALTER COLUMN "Id" DROP DEFAULT;
ALTER TABLE "People" ALTER COLUMN "Id" ADD GENERATED ALWAYS AS IDENTITY;
SELECT * FROM setval('"People_Id_seq"', nextval('"People_Id_old_seq"'), false);
DROP SEQUENCE "People_Id_old_seq";
""");
    }

    [Fact]
    public virtual async Task Alter_column_serial_change_type()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id").UseSerialColumn();
                    e.HasKey("Id");
                }),
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<long>("Id").UseSerialColumn();
                    e.HasKey("Id");
                }),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.SerialColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                Assert.Equal("bigint", column.StoreType);
                Assert.Null(column[NpgsqlAnnotationNames.IdentityOptions]);
            });

        AssertSql("""ALTER TABLE "People" ALTER COLUMN "Id" TYPE bigint;""");
    }

    [Fact]
    public virtual async Task Alter_column_restart_identity()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id").UseIdentityByDefaultColumn();
                    e.HasKey("Id");
                }),
            builder => builder.Entity("People").Property<int>("Id").HasIdentityOptions(startValue: 10),
            builder => builder.Entity("People").Property<int>("Id").HasIdentityOptions(startValue: 20),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns);
                Assert.Equal(NpgsqlValueGenerationStrategy.IdentityByDefaultColumn, column[NpgsqlAnnotationNames.ValueGenerationStrategy]);
                var options = IdentitySequenceOptionsData.Get(column);
                Assert.Equal(10, options.StartValue); // Restarting doesn't change the scaffolded start value
            });

        AssertSql("""ALTER TABLE "People" ALTER COLUMN "Id" RESTART WITH 20;""");
    }

    [Fact]
    public override async Task Alter_column_set_collation()
    {
        await base.Alter_column_set_collation();

        AssertSql("""ALTER TABLE "People" ALTER COLUMN "Name" TYPE text COLLATE "POSIX";""");
    }

    [Fact]
    public override async Task Alter_column_reset_collation()
    {
        await base.Alter_column_reset_collation();

        AssertSql("""ALTER TABLE "People" ALTER COLUMN "Name" TYPE text COLLATE "default";""");
    }

    public override async Task Convert_string_column_to_a_json_column_containing_reference()
    {
        var exception =
            await Assert.ThrowsAsync<PostgresException>(() => base.Convert_string_column_to_a_json_column_containing_reference());

        Assert.Equal("42804", exception.SqlState); // column "Name" cannot be cast automatically to type jsonb
    }

    public override async Task Convert_string_column_to_a_json_column_containing_required_reference()
    {
        var exception =
            await Assert.ThrowsAsync<PostgresException>(() => base.Convert_string_column_to_a_json_column_containing_required_reference());

        Assert.Equal("42804", exception.SqlState); // column "Name" cannot be cast automatically to type jsonb
    }

    public override async Task Convert_string_column_to_a_json_column_containing_collection()
    {
        var exception =
            await Assert.ThrowsAsync<PostgresException>(() => base.Convert_string_column_to_a_json_column_containing_collection());

        Assert.Equal("42804", exception.SqlState); // column "Name" cannot be cast automatically to type jsonb
    }

    [Fact]
    public virtual async Task Alter_column_computed_set_collation()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            return;
        }

        await Test(
            builder => builder.Entity(
                "People", b =>
                {
                    b.Property<string>("Name");
                    b.Property<string>("Name2").HasComputedColumnSql(
                        """
                        "Name"
                        """, stored: true);
                }),
            _ => { },
            builder => builder.Entity("People").Property<string>("Name2")
                .UseCollation(NonDefaultCollation),
            model =>
            {
                var computedColumn = Assert.Single(Assert.Single(model.Tables).Columns, c => c.Name == "Name2");
                Assert.Equal(
                    """
                    "Name"
                    """, computedColumn.ComputedColumnSql);
                Assert.Equal(NonDefaultCollation, computedColumn.Collation);
            });

        AssertSql("""ALTER TABLE "People" ALTER COLUMN "Name2" TYPE text COLLATE "POSIX";""");
    }

    [Fact]
    public virtual async Task Alter_column_set_compression_method()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(14))
        {
            return;
        }

        await Test(
            builder => builder.Entity("Blogs", e => e.Property<string>("Title")),
            _ => { },
            builder => builder.Entity("Blogs").Property<string>("Title").UseCompressionMethod("pglz"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "Title");
                Assert.Equal("pglz", column[NpgsqlAnnotationNames.CompressionMethod]);
            });

        AssertSql("""ALTER TABLE "Blogs" ALTER COLUMN "Title" SET COMPRESSION pglz""");
    }

    [Fact]
    public virtual async Task Alter_column_set_compression_method_to_default()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(14))
        {
            return;
        }

        await Test(
            _ => { },
            builder => builder.Entity("Blogs", e => e.Property<string>("Title").UseCompressionMethod("lz4")),
            builder => builder.Entity("Blogs").Property<string>("Title"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "Title");
                Assert.Null(column[NpgsqlAnnotationNames.CompressionMethod]);
            });

        AssertSql("""ALTER TABLE "Blogs" ALTER COLUMN "Title" SET COMPRESSION default""");
    }

    public override async Task Drop_column()
    {
        await base.Drop_column();

        AssertSql("""ALTER TABLE "People" DROP COLUMN "SomeColumn";""");
    }

    public override async Task Drop_column_primary_key()
    {
        await base.Drop_column_primary_key();

        AssertSql(
            """ALTER TABLE "People" DROP CONSTRAINT "PK_People";""",
            //
            """ALTER TABLE "People" DROP COLUMN "Id";""");
    }

    public override async Task Drop_column_computed_and_non_computed_with_dependency()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            return;
        }

        await Test(
            builder => builder.Entity("People").Property<int>("Id"),
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("X");
                    e.Property<int>("Y").HasComputedColumnSql($"{DelimitIdentifier("X")} + 1", stored: true);
                }),
            _ => { },
            model =>
            {
                var table = Assert.Single(model.Tables);
                Assert.Equal("Id", Assert.Single(table.Columns).Name);
            });

        AssertSql(
            """ALTER TABLE "People" DROP COLUMN "Y";""",
            //
            """ALTER TABLE "People" DROP COLUMN "X";""");
    }

    [Fact]
    public virtual async Task Drop_column_system()
    {
        // System columns (e.g. xmin) are implicitly always present. If an xmin property is removed,
        // nothing should happen.
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<uint>("xmin");
                    e.HasKey("Id");
                }),
            _ => { },
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.HasKey("Id");
                }),
            model =>
            {
                var table = Assert.Single(model.Tables);
                Assert.Equal("Id", Assert.Single(table.Columns).Name);
            });

        AssertSql();
    }

    public override async Task Rename_column()
    {
        await base.Rename_column();

        AssertSql("""ALTER TABLE "People" RENAME COLUMN "SomeColumn" TO "SomeOtherColumn";""");
    }

    #endregion

    #region Index

    public override async Task Create_index_unique()
    {
        await base.Create_index_unique();

        AssertSql("""CREATE UNIQUE INDEX "IX_People_FirstName_LastName" ON "People" ("FirstName", "LastName");""");
    }

    public override async Task Create_index_descending()
    {
        await base.Create_index_descending();

        AssertSql("""CREATE INDEX "IX_People_X" ON "People" ("X" DESC);""");
    }

    public override async Task Create_index_descending_mixed()
    {
        await base.Create_index_descending_mixed();

        AssertSql("""CREATE INDEX "IX_People_X_Y_Z" ON "People" ("X", "Y" DESC, "Z");""");
    }

    [Fact]
    public virtual async Task Create_index_descending_mixed_legacy_annotation()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("X");
                    e.Property<int>("Y");
                    e.Property<int>("Z");
                }),
            builder => { },
            builder => builder.Entity("People")
                .HasIndex("X", "Y", "Z")
                .HasAnnotation(NpgsqlAnnotationNames.IndexSortOrder, new[] { SortOrder.Ascending, SortOrder.Descending }),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Collection(index.IsDescending, Assert.False, Assert.True, Assert.False);
            });

        AssertSql("""CREATE INDEX "IX_People_X_Y_Z" ON "People" ("X", "Y" DESC, "Z");""");
    }

    public override async Task Create_index_with_filter()
    {
        await base.Create_index_with_filter();

        AssertSql("""CREATE INDEX "IX_People_Name" ON "People" ("Name") WHERE "Name" IS NOT NULL;""");
    }

    public override async Task Create_unique_index_with_filter()
    {
        await base.Create_unique_index_with_filter();

        AssertSql("""CREATE UNIQUE INDEX "IX_People_Name" ON "People" ("Name") WHERE "Name" IS NOT NULL AND "Name" <> '';""");
    }

    [Fact]
    public virtual async Task Create_index_with_include()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<string>("FirstName");
                    e.Property<string>("LastName").HasColumnName("last_name");
                    e.Property<string>("Name");
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("Name")
                .IncludeProperties("FirstName", "LastName"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Single(index.Columns);
                Assert.Contains(table.Columns.Single(c => c.Name == "Name"), index.Columns);

                // Scaffolding included/covered properties is currently blocked, see #2194
                var includedColumns = (string[])index[NpgsqlAnnotationNames.IndexInclude]!;
                Assert.Null(includedColumns);

                // if (TestEnvironment.PostgresVersion.AtLeast(11))
                // {
                //     Assert.Contains("FirstName", includedColumns);
                //     Assert.Contains("last_name", includedColumns);
                // }
                // else
                // {
                //     Assert.Null(includedColumns);
                // }
            });

        AssertSql(
            TestEnvironment.PostgresVersion.AtLeast(11)
                ? """CREATE INDEX "IX_People_Name" ON "People" ("Name") INCLUDE ("FirstName", last_name);"""
                : """CREATE INDEX "IX_People_Name" ON "People" ("Name");""");
    }

    [Fact]
    public virtual async Task Create_index_with_include_and_filter()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<string>("FirstName");
                    e.Property<string>("LastName");
                    e.Property<string>("Name");
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("Name")
                .IncludeProperties("FirstName", "LastName")
                .HasFilter(
                    """
                    "Name" IS NOT NULL
                    """),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Equal("""("Name" IS NOT NULL)""", index.Filter);
                Assert.Single(index.Columns);
                Assert.Contains(table.Columns.Single(c => c.Name == "Name"), index.Columns);

                // Scaffolding included/covered properties is currently blocked, see #2194
                var includedColumns = (string[])index[NpgsqlAnnotationNames.IndexInclude]!;
                Assert.Null(includedColumns);

                // if (TestEnvironment.PostgresVersion.AtLeast(11))
                // {
                //     Assert.Contains("FirstName", includedColumns);
                //     Assert.Contains("LastName", includedColumns);
                // }
                // else
                // {
                //     Assert.Null(includedColumns);
                // }
            });

        AssertSql(
            TestEnvironment.PostgresVersion.AtLeast(11)
                ? """CREATE INDEX "IX_People_Name" ON "People" ("Name") INCLUDE ("FirstName", "LastName") WHERE "Name" IS NOT NULL;"""
                : """CREATE INDEX "IX_People_Name" ON "People" ("Name") WHERE "Name" IS NOT NULL;""");
    }

    [Fact]
    public virtual async Task Create_index_unique_with_include()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<string>("FirstName");
                    e.Property<string>("LastName");
                    e.Property<string>("Name").IsRequired();
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("Name")
                .IsUnique()
                .IncludeProperties("FirstName", "LastName"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.True(index.IsUnique);
                Assert.Single(index.Columns);
                Assert.Contains(table.Columns.Single(c => c.Name == "Name"), index.Columns);

                // Scaffolding included/covered properties is currently blocked, see #2194
                var includedColumns = (string[])index[NpgsqlAnnotationNames.IndexInclude]!;
                Assert.Null(includedColumns);

                // if (TestEnvironment.PostgresVersion.AtLeast(11))
                // {
                //     Assert.Contains("FirstName", includedColumns);
                //     Assert.Contains("LastName", includedColumns);
                // }
                // else
                // {
                //     Assert.Null(includedColumns);
                // }
            });

        AssertSql(
            TestEnvironment.PostgresVersion.AtLeast(11)
                ? """CREATE UNIQUE INDEX "IX_People_Name" ON "People" ("Name") INCLUDE ("FirstName", "LastName");"""
                : """CREATE UNIQUE INDEX "IX_People_Name" ON "People" ("Name");""");
    }

    [Fact]
    public virtual async Task Create_index_unique_with_include_and_filter()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<string>("FirstName");
                    e.Property<string>("LastName");
                    e.Property<string>("Name").IsRequired();
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("Name")
                .IsUnique()
                .IncludeProperties("FirstName", "LastName")
                .HasFilter(
                    """
                    "Name" IS NOT NULL
                    """),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.True(index.IsUnique);
                Assert.Equal("""("Name" IS NOT NULL)""", index.Filter);
                Assert.Single(index.Columns);
                Assert.Contains(table.Columns.Single(c => c.Name == "Name"), index.Columns);

                // Scaffolding included/covered properties is currently blocked, see #2194
                var includedColumns = (string[])index[NpgsqlAnnotationNames.IndexInclude]!;
                Assert.Null(includedColumns);

                // if (TestEnvironment.PostgresVersion.AtLeast(11))
                // {
                //     Assert.Contains("FirstName", includedColumns);
                //     Assert.Contains("LastName", includedColumns);
                // }
                // else
                // {
                //     Assert.Null(includedColumns);
                // }
            });

        AssertSql(
            TestEnvironment.PostgresVersion.AtLeast(11)
                ? """CREATE UNIQUE INDEX "IX_People_Name" ON "People" ("Name") INCLUDE ("FirstName", "LastName") WHERE "Name" IS NOT NULL;"""
                : """CREATE UNIQUE INDEX "IX_People_Name" ON "People" ("Name") WHERE "Name" IS NOT NULL;""");
    }

    [Fact]
    public virtual async Task Create_index_concurrently()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("Age");
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("Age")
                .IsCreatedConcurrently(),
            asserter: null); // No scaffolding for IsCreatedConcurrently

        AssertSql("""CREATE INDEX CONCURRENTLY "IX_People_Age" ON "People" ("Age");""");
    }

    [Fact]
    public virtual async Task Create_index_with_method()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("Age");
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("Age")
                .HasMethod("hash"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Equal("hash", index[NpgsqlAnnotationNames.IndexMethod]);
            });

        AssertSql("""CREATE INDEX "IX_People_Age" ON "People" USING hash ("Age");""");
    }

    [Fact]
    public virtual async Task Create_index_with_operators()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<string>("FirstName");
                    e.Property<string>("LastName");
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("FirstName", "LastName")
                .HasOperators("text_pattern_ops"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Equal(new[] { "text_pattern_ops", null }, index[NpgsqlAnnotationNames.IndexOperators]);
            });

        AssertSql("""CREATE INDEX "IX_People_FirstName_LastName" ON "People" ("FirstName" text_pattern_ops, "LastName");""");
    }

    [Fact]
    public virtual async Task Create_index_with_collation()
    {
        await Test(
            builder =>
            {
                builder.Entity("People", e => e.Property<string>("Name"));
                builder.HasCollation("some_collation", locale: "POSIX", provider: "libc");
            },
            _ => { },
            builder => builder.Entity("People").HasIndex("Name").UseCollation("some_collation"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Equal("some_collation", Assert.Single((IReadOnlyList<string>)index[RelationalAnnotationNames.Collation]!));
            });

        AssertSql("""CREATE INDEX "IX_People_Name" ON "People" ("Name" COLLATE some_collation);""");
    }

    [Fact] // #3027
    public virtual async Task Create_index_with_collation_and_operators()
    {
        await Test(
            builder =>
            {
                builder.Entity("People", e => e.Property<string>("Name"));
                builder.HasCollation("some_collation", locale: "POSIX", provider: "libc");
            },
            _ => { },
            builder => builder.Entity("People").HasIndex("Name")
                .UseCollation("some_collation")
                .HasOperators("text_pattern_ops"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Equal("text_pattern_ops", Assert.Single((IReadOnlyList<string>)index[NpgsqlAnnotationNames.IndexOperators]!));
                Assert.Equal("some_collation", Assert.Single((IReadOnlyList<string>)index[RelationalAnnotationNames.Collation]!));
            });

        AssertSql("""CREATE INDEX "IX_People_Name" ON "People" ("Name" COLLATE some_collation text_pattern_ops);""");
    }

    [Fact]
    public virtual async Task Create_index_with_null_sort_order()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<string>("FirstName");
                    e.Property<string>("MiddleName");
                    e.Property<string>("LastName");
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("FirstName", "MiddleName", "LastName")
                .HasNullSortOrder(NullSortOrder.NullsFirst, NullSortOrder.Unspecified, NullSortOrder.NullsLast),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                Assert.Equal(
                    new[] { NullSortOrder.NullsFirst, NullSortOrder.NullsLast, NullSortOrder.NullsLast },
                    index[NpgsqlAnnotationNames.IndexNullSortOrder]);
            });

        AssertSql(
            """CREATE INDEX "IX_People_FirstName_MiddleName_LastName" ON "People" ("FirstName" NULLS FIRST, "MiddleName", "LastName" NULLS LAST);""");
    }

    [Fact]
    public virtual async Task Create_index_tsvector()
    {
        await Test(
            builder => builder.Entity(
                "Blogs", e =>
                {
                    e.Property<string>("Title").IsRequired();
                    e.Property<string>("Description");
                }),
            _ => { },
            builder => builder.Entity("Blogs")
                .HasIndex("Title", "Description")
                .IsTsVectorExpressionIndex("simple"),
            _ => { });

        AssertSql(
            """CREATE INDEX "IX_Blogs_Title_Description" ON "Blogs" (to_tsvector('simple', "Title" || ' ' || coalesce("Description", '')));""");
    }

    [Fact]
    public virtual async Task Create_index_tsvector_using_gin()
    {
        await Test(
            builder => builder.Entity(
                "Blogs", e =>
                {
                    e.Property<string>("Title").IsRequired();
                    e.Property<string>("Description");
                }),
            _ => { },
            builder => builder.Entity("Blogs")
                .HasIndex("Title", "Description")
                .HasMethod("GIN")
                .IsTsVectorExpressionIndex("simple"),
            _ => { });

        AssertSql(
            """CREATE INDEX "IX_Blogs_Title_Description" ON "Blogs" USING GIN (to_tsvector('simple', "Title" || ' ' || coalesce("Description", '')));""");
    }

    [ConditionalFact]
    [MinimumPostgresVersion(15, 0)]
    public virtual async Task Create_index_with_nulls_not_distinct()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("Age");
                }),
            _ => { },
            builder => builder.Entity(
                "People", b =>
                {
                    b.HasIndex(["Age"], "IX_NullsDistinct")
                        .IsUnique();

                    b.HasIndex(["Age"], "IX_NullsNotDistinct")
                        .IsUnique()
                        .AreNullsDistinct(false);
                }),
            model =>
            {
                var table = Assert.Single(model.Tables);

                Assert.Null(
                    Assert.Single(table.Indexes, i => i.Name == "IX_NullsDistinct")[NpgsqlAnnotationNames.NullsDistinct]);

                Assert.Equal(
                    false,
                    Assert.Single(table.Indexes, i => i.Name == "IX_NullsNotDistinct")[NpgsqlAnnotationNames.NullsDistinct]);
            });

        AssertSql(
            """CREATE UNIQUE INDEX "IX_NullsDistinct" ON "People" ("Age");""",
            //
            """CREATE UNIQUE INDEX "IX_NullsNotDistinct" ON "People" ("Age") NULLS NOT DISTINCT;""");
    }

    [Fact]
    public virtual async Task Create_index_with_storage_parameter()
    {
        await Test(
            builder => builder.Entity(
                "People", e =>
                {
                    e.Property<int>("Id");
                    e.Property<int>("Age");
                }),
            _ => { },
            builder => builder.Entity("People").HasIndex("Age")
                .HasStorageParameter("fillfactor", 70),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var index = Assert.Single(table.Indexes);
                var storageParameter = Assert.Single(
                    index.GetAnnotations(),
                    a => a.Name.StartsWith(NpgsqlAnnotationNames.StorageParameterPrefix, StringComparison.Ordinal));

                Assert.Equal(NpgsqlAnnotationNames.StorageParameterPrefix + "fillfactor", storageParameter.Name);
                // Storage parameter values always get scaffolded as strings (PG storage is simply 'name=value')
                Assert.Equal("70", storageParameter.Value);
            });

        AssertSql("""CREATE INDEX "IX_People_Age" ON "People" ("Age") WITH (fillfactor=70);""");
    }

    public override async Task Alter_index_change_sort_order()
    {
        await base.Alter_index_change_sort_order();

        AssertSql(
            """DROP INDEX "IX_People_X_Y_Z";""",
            //
            """CREATE INDEX "IX_People_X_Y_Z" ON "People" ("X", "Y" DESC, "Z");""");
    }

    public override async Task Drop_index()
    {
        await base.Drop_index();

        AssertSql("""DROP INDEX "IX_People_SomeField";""");
    }

    public override async Task Rename_index()
    {
        await base.Rename_index();

        AssertSql("""ALTER INDEX "Foo" RENAME TO foo;""");
    }

    #endregion

    #region Key and constraint

    public override async Task Add_primary_key_int()
    {
        await base.Add_primary_key_int();

        AssertSql(
            """
ALTER TABLE "People" ALTER COLUMN "SomeField" DROP DEFAULT;
ALTER TABLE "People" ALTER COLUMN "SomeField" ADD GENERATED BY DEFAULT AS IDENTITY;
""",
            //
            """
ALTER TABLE "People" ADD CONSTRAINT "PK_People" PRIMARY KEY ("SomeField");
""");
    }

    public override async Task Add_primary_key_string()
    {
        await base.Add_primary_key_string();

        AssertSql("""ALTER TABLE "People" ADD CONSTRAINT "PK_People" PRIMARY KEY ("SomeField");""");
    }

    public override async Task Add_primary_key_with_name()
    {
        await base.Add_primary_key_with_name();

        AssertSql(
            """
UPDATE "People" SET "SomeField" = '' WHERE "SomeField" IS NULL;
ALTER TABLE "People" ALTER COLUMN "SomeField" SET NOT NULL;
ALTER TABLE "People" ALTER COLUMN "SomeField" SET DEFAULT '';
""",
            //
            """
ALTER TABLE "People" ADD CONSTRAINT "PK_Foo" PRIMARY KEY ("SomeField");
""");
    }

    public override async Task Add_primary_key_composite_with_name()
    {
        await base.Add_primary_key_composite_with_name();

        AssertSql("""ALTER TABLE "People" ADD CONSTRAINT "PK_Foo" PRIMARY KEY ("SomeField1", "SomeField2");""");
    }

    public override async Task Drop_primary_key_int()
    {
        await base.Drop_primary_key_int();

        AssertSql(
            """ALTER TABLE "People" DROP CONSTRAINT "PK_People";""",
            //
            """ALTER TABLE "People" ALTER COLUMN "SomeField" DROP IDENTITY;""");
    }

    public override async Task Drop_primary_key_string()
    {
        await base.Drop_primary_key_string();

        AssertSql("""ALTER TABLE "People" DROP CONSTRAINT "PK_People";""");
    }

    public override Task Add_foreign_key()
        => Task.CompletedTask; // https://github.com/npgsql/efcore.pg/issues/1217

    public override async Task Add_foreign_key_with_name()
    {
        await base.Add_foreign_key_with_name();

        AssertSql(
            """CREATE INDEX "IX_Orders_CustomerId" ON "Orders" ("CustomerId");""",
            //
            """ALTER TABLE "Orders" ADD CONSTRAINT "FK_Foo" FOREIGN KEY ("CustomerId") REFERENCES "Customers" ("Id") ON DELETE CASCADE;""");
    }

    public override async Task Drop_foreign_key()
    {
        await base.Drop_foreign_key();

        AssertSql(
            """ALTER TABLE "Orders" DROP CONSTRAINT "FK_Orders_Customers_CustomerId";""",
            //
            """DROP INDEX "IX_Orders_CustomerId";""");
    }

    public override async Task Add_unique_constraint()
    {
        await base.Add_unique_constraint();

        AssertSql("""ALTER TABLE "People" ADD CONSTRAINT "AK_People_AlternateKeyColumn" UNIQUE ("AlternateKeyColumn");""");
    }

    public override async Task Add_unique_constraint_composite_with_name()
    {
        await base.Add_unique_constraint_composite_with_name();

        AssertSql("""ALTER TABLE "People" ADD CONSTRAINT "AK_Foo" UNIQUE ("AlternateKeyColumn1", "AlternateKeyColumn2");""");
    }

    public override async Task Drop_unique_constraint()
    {
        await base.Drop_unique_constraint();

        AssertSql("""ALTER TABLE "People" DROP CONSTRAINT "AK_People_AlternateKeyColumn";""");
    }

    public override async Task Add_check_constraint_with_name()
    {
        await base.Add_check_constraint_with_name();

        AssertSql("""ALTER TABLE "People" ADD CONSTRAINT "CK_People_Foo" CHECK ("DriverLicense" > 0);""");
    }

    public override async Task Drop_check_constraint()
    {
        await base.Drop_check_constraint();

        AssertSql("""ALTER TABLE "People" DROP CONSTRAINT "CK_People_Foo";""");
    }

    [Theory]
    [InlineData(PostgresMatchStrategy.Simple, "SIMPLE", false)]
    [InlineData(PostgresMatchStrategy.Partial, "PARTIAL", true)]
    [InlineData(PostgresMatchStrategy.Full, "FULL", false)]
    public async Task Add_foreign_key_with_match_strategy(PostgresMatchStrategy strategy, string matchValue, bool throws)
    {
        var runningTest = Test(
            builder =>
            {
                builder.Entity(
                    "Customers", delegate(EntityTypeBuilder e)
                    {
                        e.Property<int>("Id");
                        e.HasKey("Id");
                        e.Property<int>("AddressId");
                    });
                builder.Entity(
                    "Orders", delegate(EntityTypeBuilder e)
                    {
                        e.Property<int>("Id");
                        e.Property<int>("CustomerId");
                    });
            },
            _ => { },
            builder =>
            {
                builder.Entity("Orders")
                    .HasOne("Customers")
                    .WithMany()
                    .HasForeignKey("CustomerId")
                    .UsesMatchStrategy(strategy)
                    .HasConstraintName("FK_Foo");
            },
            asserter: null);

        if (throws)
        {
            await Assert.ThrowsAsync<PostgresException>(() => runningTest);
        }
        else
        {
            await runningTest;
        }

        AssertSql(
            """
CREATE INDEX "IX_Orders_CustomerId" ON "Orders" ("CustomerId");
""",
            //
            $"""
ALTER TABLE "Orders" ADD CONSTRAINT "FK_Foo" FOREIGN KEY ("CustomerId") REFERENCES "Customers" ("Id") MATCH {matchValue} ON DELETE CASCADE;
""");
    }

    #endregion

    #region Sequence

    public override async Task Create_sequence()
    {
        await base.Create_sequence();

        AssertSql("""CREATE SEQUENCE "TestSequence" AS integer START WITH 1 INCREMENT BY 1 NO CYCLE;""");
    }

    public override async Task Create_sequence_all_settings()
    {
        await base.Create_sequence_all_settings();

        AssertSql(
            """
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'dbo2') THEN
        CREATE SCHEMA dbo2;
    END IF;
END $EF$;
""",
            //
            """
CREATE SEQUENCE dbo2."TestSequence" START WITH 3 INCREMENT BY 2 MINVALUE 2 MAXVALUE 916 CYCLE;
""");
    }

    [Fact]
    public virtual async Task Create_sequence_smallint()
    {
        await Test(
            _ => { },
            builder => builder.HasSequence<short>("TestSequence"),
            model =>
            {
                var sequence = Assert.Single(model.Sequences);
                Assert.Equal("TestSequence", sequence.Name);
                Assert.Equal("smallint", sequence.StoreType);
            });

        AssertSql("""CREATE SEQUENCE "TestSequence" AS smallint START WITH 1 INCREMENT BY 1 NO CYCLE;""");
    }

    [Fact]
    public override async Task Alter_sequence_all_settings()
    {
        await base.Alter_sequence_all_settings();

        AssertSql(
            """
ALTER SEQUENCE foo INCREMENT BY 2 MINVALUE -5 MAXVALUE 10 CYCLE;
""",
            //
            """
ALTER SEQUENCE foo START WITH -3;
ALTER SEQUENCE foo RESTART;
""");
    }

    public override async Task Alter_sequence_increment_by()
    {
        await base.Alter_sequence_increment_by();

        AssertSql(
            """
ALTER SEQUENCE foo INCREMENT BY 2 NO MINVALUE NO MAXVALUE NO CYCLE;
""");
    }

    public override async Task Alter_sequence_restart_with()
    {
        await base.Alter_sequence_restart_with();

        AssertSql(
            """
ALTER SEQUENCE foo START WITH 3;
ALTER SEQUENCE foo RESTART;
""");
    }

    public override async Task Drop_sequence()
    {
        await base.Drop_sequence();

        AssertSql("""DROP SEQUENCE "TestSequence";""");
    }

    public override async Task Rename_sequence()
    {
        await base.Rename_sequence();

        AssertSql("""ALTER SEQUENCE "TestSequence" RENAME TO testsequence;""");
    }

    public override async Task Move_sequence()
    {
        await base.Move_sequence();

        AssertSql(
            """
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'TestSequenceSchema') THEN
        CREATE SCHEMA "TestSequenceSchema";
    END IF;
END $EF$;
""",
            //
            """
ALTER SEQUENCE "TestSequence" SET SCHEMA "TestSequenceSchema";
""");
    }

    #endregion

    #region Data seeding

    public override async Task InsertDataOperation()
    {
        await base.InsertDataOperation();

        AssertSql(
            """
INSERT INTO "Person" ("Id", "Name")
VALUES (1, 'Daenerys Targaryen');
INSERT INTO "Person" ("Id", "Name")
VALUES (2, 'John Snow');
INSERT INTO "Person" ("Id", "Name")
VALUES (3, 'Arya Stark');
INSERT INTO "Person" ("Id", "Name")
VALUES (4, 'Harry Strickland');
INSERT INTO "Person" ("Id", "Name")
VALUES (5, NULL);
""",
            //
            """
SELECT setval(
    pg_get_serial_sequence('"Person"', 'Id'),
    GREATEST(
        (SELECT MAX("Id") FROM "Person") + 1,
        nextval(pg_get_serial_sequence('"Person"', 'Id'))),
    false);
""");
    }

    public override async Task DeleteDataOperation_simple_key()
    {
        await base.DeleteDataOperation_simple_key();

        AssertSql(
            """
DELETE FROM "Person"
WHERE "Id" = 2;
""");
    }

    public override async Task DeleteDataOperation_composite_key()
    {
        await base.DeleteDataOperation_composite_key();

        AssertSql(
            """
DELETE FROM "Person"
WHERE "AnotherId" = 12 AND "Id" = 2;
""");
    }

    public override async Task UpdateDataOperation_simple_key()
    {
        await base.UpdateDataOperation_simple_key();

        AssertSql(
            """
UPDATE "Person" SET "Name" = 'Another John Snow'
WHERE "Id" = 2;
""");
    }

    public override async Task UpdateDataOperation_composite_key()
    {
        await base.UpdateDataOperation_composite_key();

        AssertSql(
            """
UPDATE "Person" SET "Name" = 'Another John Snow'
WHERE "AnotherId" = 11 AND "Id" = 2;
""");
    }

    public override async Task UpdateDataOperation_multiple_columns()
    {
        await base.UpdateDataOperation_multiple_columns();

        AssertSql(
            """
UPDATE "Person" SET "Age" = 21, "Name" = 'Another John Snow'
WHERE "Id" = 2;
""");
    }

    [ConditionalFact]
    public virtual async Task InsertDataOperation_restarts_identity()
    {
        await Test(
            builder =>
            {
                builder.Entity(
                    "Person", e =>
                    {
                        e.Property<int>("Id").UseIdentityByDefaultColumn();
                        e.Property<string>("Name");
                        e.HasKey("Id");
                    });
                builder.Entity(
                    "Person2", e =>
                    {
                        e.Property<int>("Id").UseIdentityByDefaultColumn();
                        e.Property<string>("Name");
                        e.HasKey("Id");
                    });
            },
            _ => { },
            builder =>
            {
                builder.Entity("Person").HasData(
                    new { Id = 1, Name = "Daenerys Targaryen" },
                    new { Id = 2, Name = "John Snow" });
                builder.Entity("Person2").HasData(
                    new { Id = -10, Name = "Daenerys Targaryen" },
                    new { Id = -20, Name = "John Snow" });
            },
            _ => { });

        AssertSql(
            """
INSERT INTO "Person" ("Id", "Name")
VALUES (1, 'Daenerys Targaryen');
INSERT INTO "Person" ("Id", "Name")
VALUES (2, 'John Snow');
""",
            //
            """
INSERT INTO "Person2" ("Id", "Name")
VALUES (-20, 'John Snow');
INSERT INTO "Person2" ("Id", "Name")
VALUES (-10, 'Daenerys Targaryen');
""",
            //
            """
SELECT setval(
    pg_get_serial_sequence('"Person"', 'Id'),
    GREATEST(
        (SELECT MAX("Id") FROM "Person") + 1,
        nextval(pg_get_serial_sequence('"Person"', 'Id'))),
    false);
SELECT setval(
    pg_get_serial_sequence('"Person2"', 'Id'),
    GREATEST(
        (SELECT MAX("Id") FROM "Person2") + 1,
        nextval(pg_get_serial_sequence('"Person2"', 'Id'))),
    false);
""");
    }

    #endregion Data seeding

    public override async Task Add_required_primitve_collection_with_custom_default_value_sql_to_existing_table()
    {
        await base.Add_required_primitve_collection_with_custom_default_value_sql_to_existing_table_core("ARRAY[3, 2, 1]");

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" integer[] NOT NULL DEFAULT (ARRAY[3, 2, 1]);""");
    }

    #region PostgreSQL extensions

    [Fact]
    public virtual async Task Ensure_postgres_extension()
    {
        await Test(
            _ => { },
            builder => builder.HasPostgresExtension("citext"),
            model =>
            {
                var citext = Assert.Single(model.GetPostgresExtensions());
                Assert.Equal("citext", citext.Name);
                Assert.Equal("public", citext.Schema);
            });

        AssertSql("CREATE EXTENSION IF NOT EXISTS citext;");
    }

    [Fact]
    public virtual async Task Ensure_postgres_extension_with_schema()
    {
        await Test(
            _ => { },
            builder => builder.HasPostgresExtension("some_schema", "citext"),
            model =>
            {
                var citext = Assert.Single(model.GetPostgresExtensions());
                Assert.Equal("citext", citext.Name);
                Assert.Equal("some_schema", citext.Schema);
            });

        AssertSql(
            """
                DO $EF$
                BEGIN
                    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'some_schema') THEN
                        CREATE SCHEMA some_schema;
                    END IF;
                END $EF$;
                """,
            //
            @"CREATE EXTENSION IF NOT EXISTS citext SCHEMA some_schema;");
    }

    #endregion

    #region PostgreSQL enums

    [Fact]
    public virtual async Task Create_enum()
    {
        await Test(
            _ => { },
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Sad"]),
            model =>
            {
                var moodEnum = Assert.Single(model.GetPostgresEnums());
                Assert.Equal("Mood", moodEnum.Name);
                Assert.Null(moodEnum.Schema);
                Assert.Collection(
                    moodEnum.Labels,
                    l => Assert.Equal("Happy", l),
                    l => Assert.Equal("Sad", l));
            });

        AssertSql("""CREATE TYPE "Mood" AS ENUM ('Happy', 'Sad');""");
    }

    [Fact]
    public virtual async Task Create_enum_with_schema()
    {
        await Test(
            _ => { },
            builder => builder.HasPostgresEnum("some_schema", "Mood", ["Happy", "Sad"]),
            model =>
            {
                var moodEnum = Assert.Single(model.GetPostgresEnums());
                Assert.Equal("Mood", moodEnum.Name);
                Assert.Equal("some_schema", moodEnum.Schema);
                Assert.Collection(
                    moodEnum.Labels,
                    l => Assert.Equal("Happy", l),
                    l => Assert.Equal("Sad", l));
            });

        AssertSql(
            """
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'some_schema') THEN
        CREATE SCHEMA some_schema;
    END IF;
END $EF$;
""",
            //
            """
CREATE TYPE some_schema."Mood" AS ENUM ('Happy', 'Sad');
""");
    }

    [Fact]
    public virtual async Task Drop_enum()
    {
        await Test(
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Sad"]),
            _ => { },
            model => Assert.Empty(model.GetPostgresEnums()));

        AssertSql("""DROP TYPE "Mood";""");
    }

    [Fact] // #979
    public virtual async Task Do_not_alter_existing_enum_when_creating_new_one()
    {
        await Test(
            builder => builder.HasPostgresEnum("Enum1", ["A", "B"]),
            _ => { },
            builder => builder.HasPostgresEnum("Enum2", ["X", "Y"]),
            model => Assert.Equal(2, model.GetPostgresEnums().Count()));

        AssertSql("""CREATE TYPE "Enum2" AS ENUM ('X', 'Y');""");
    }

    [Fact]
    public virtual async Task Alter_enum_add_label_at_end()
    {
        await Test(
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Sad"]),
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Sad", "Angry"]),
            model =>
            {
                var moodEnum = Assert.Single(model.GetPostgresEnums());
                Assert.Collection(
                    moodEnum.Labels,
                    l => Assert.Equal("Happy", l),
                    l => Assert.Equal("Sad", l),
                    l => Assert.Equal("Angry", l));
            });

        AssertSql("""ALTER TYPE "Mood" ADD VALUE 'Angry';""");
    }

    [Fact]
    public virtual async Task Alter_enum_add_label_in_middle()
    {
        await Test(
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Sad"]),
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Angry", "Sad"]),
            model =>
            {
                var moodEnum = Assert.Single(model.GetPostgresEnums());
                Assert.Collection(
                    moodEnum.Labels,
                    l => Assert.Equal("Happy", l),
                    l => Assert.Equal("Angry", l),
                    l => Assert.Equal("Sad", l));
            });

        AssertSql("""ALTER TYPE "Mood" ADD VALUE 'Angry' BEFORE 'Sad';""");
    }

    [Fact]
    public virtual Task Alter_enum_drop_label_not_supported()
        => TestThrows<NotSupportedException>(
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Sad"]),
            builder => builder.HasPostgresEnum("Mood", ["Happy"]));

    [Fact]
    public virtual Task Alter_enum_change_label_not_supported()
        => TestThrows<NotSupportedException>(
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Sad"]),
            builder => builder.HasPostgresEnum("Mood", ["Happy", "Angry"]));

    #endregion

    #region PostgreSQL collation management

    [Fact]
    public virtual async Task Create_collation()
    {
        await Test(
            _ => { },
            builder => builder.HasCollation("dummy", locale: "POSIX", provider: "libc"),
            model =>
            {
                var collation = Assert.Single(PostgresCollation.GetCollations(model));

                Assert.Equal("dummy", collation.Name);
                Assert.Equal("libc", collation.Provider);
                Assert.Equal("POSIX", collation.LcCollate);
                Assert.Equal("POSIX", collation.LcCtype);
                Assert.True(collation.IsDeterministic);
            });

        AssertSql(
            """
CREATE COLLATION dummy (LOCALE = 'POSIX',
    PROVIDER = libc
);
""");
    }

    [ConditionalFact]
    [MinimumPostgresVersion(12, 0)]
    public virtual async Task Create_collation_non_deterministic()
    {
        await Test(
            _ => { },
            builder => builder.HasCollation("some_collation", locale: "en-u-ks-level1", provider: "icu", deterministic: false),
            model =>
            {
                var collation = Assert.Single(PostgresCollation.GetCollations(model));

                Assert.Equal("some_collation", collation.Name);
                Assert.Equal("icu", collation.Provider);
                Assert.Equal("en-u-ks-level1", collation.LcCollate);
                Assert.Equal("en-u-ks-level1", collation.LcCtype);
                Assert.False(collation.IsDeterministic);
            });

        AssertSql(
            """
CREATE COLLATION some_collation (LOCALE = 'en-u-ks-level1',
    PROVIDER = icu,
    DETERMINISTIC = False
);
""");
    }

    [Fact]
    public virtual async Task Drop_collation()
    {
        await Test(
            builder => builder.HasCollation("dummy", locale: "POSIX", provider: "libc"),
            _ => { },
            model => Assert.Empty(PostgresCollation.GetCollations(model)));

        AssertSql("""DROP COLLATION dummy;""");
    }

    [Fact]
    public virtual Task Alter_collation_throws()
        => TestThrows<NotSupportedException>(
            builder => builder.HasCollation("dummy", locale: "POSIX", provider: "libc"),
            builder => builder.HasCollation("dummy", locale: "C", provider: "libc"));

    #endregion PostgreSQL collation management

    #region PostgreSQL full-text search

    [Fact]
    public virtual async Task Add_column_generated_tsvector_over_text()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            return;
        }

        await Test(
            builder => builder.Entity("Blogs", e => e.Property<string>("TextColumn").IsRequired()),
            _ => { },
            builder => builder.Entity("Blogs").Property<NpgsqlTsVector>("SearchColumn").IsGeneratedTsVectorColumn("english", "TextColumn"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SearchColumn");
                Assert.Equal("tsvector", column.StoreType);
            });

        AssertSql("""ALTER TABLE "Blogs" ADD "SearchColumn" tsvector GENERATED ALWAYS AS (to_tsvector('english', "TextColumn")) STORED;""");
    }

    [Fact]
    public virtual async Task Add_column_generated_tsvector_over_jsonb()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            return;
        }

        await Test(
            builder => builder.Entity("People").Property<string>("JsonbColumn").HasColumnType("jsonb").IsRequired(),
            _ => { },
            builder => builder.Entity("People").Property<NpgsqlTsVector>("SearchColumn")
                .IsGeneratedTsVectorColumn("english", "JsonbColumn"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SearchColumn");
                Assert.Equal("tsvector", column.StoreType);
            });

        AssertSql(
            """ALTER TABLE "People" ADD "SearchColumn" tsvector GENERATED ALWAYS AS (jsonb_to_tsvector('english', "JsonbColumn", '"all"')) STORED;""");
    }

    [Fact]
    public virtual async Task Add_column_generated_tsvector_over_mixed()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            return;
        }

        await Test(
            builder =>
            {
                builder.Entity("People").Property<string>("RequiredTextColumn").IsRequired();
                builder.Entity("People").Property<string>("OptionalTextColumn");
                builder.Entity("People").Property<string>("RequiredJsonbColumn").HasColumnType("jsonb").IsRequired();
                builder.Entity("People").Property<string>("OptionalJsonColumn").HasColumnType("json");
            },
            _ => { },
            builder => builder.Entity("People").Property<NpgsqlTsVector>("SearchColumn")
                .IsGeneratedTsVectorColumn(
                    "english", "RequiredTextColumn", "OptionalTextColumn", "RequiredJsonbColumn", "OptionalJsonColumn"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "SearchColumn");
                Assert.Equal("tsvector", column.StoreType);
            });

        AssertSql(
            """ALTER TABLE "People" ADD "SearchColumn" tsvector GENERATED ALWAYS AS (to_tsvector('english', "RequiredTextColumn" || ' ' || coalesce("OptionalTextColumn", '')) || jsonb_to_tsvector('english', "RequiredJsonbColumn", '"all"') || json_to_tsvector('english', coalesce("OptionalJsonColumn", '{}'), '"all"')) STORED;""");
    }

    [Fact]
    public virtual async Task Alter_column_generated_tsvector_change_config()
    {
        if (TestEnvironment.PostgresVersion.IsUnder(12))
        {
            return;
        }

        await Test(
            builder => builder.Entity(
                "Blogs", e =>
                {
                    e.Property<string>("Title").IsRequired();
                    e.Property<string>("Description");
                }),
            builder => builder.Entity("Blogs").Property<NpgsqlTsVector>("TsVector")
                .IsGeneratedTsVectorColumn("german", "Title", "Description"),
            builder => builder.Entity("Blogs").Property<NpgsqlTsVector>("TsVector")
                .IsGeneratedTsVectorColumn("english", "Title", "Description"),
            model =>
            {
                var table = Assert.Single(model.Tables);
                var column = Assert.Single(table.Columns, c => c.Name == "TsVector");
                Assert.Equal("tsvector", column.StoreType);
                Assert.Equal(
                    """to_tsvector('english'::regconfig, (("Title" || ' '::text) || COALESCE("Description", ''::text)))""",
                    column.ComputedColumnSql);
            });

        AssertSql(
            """ALTER TABLE "Blogs" DROP COLUMN "TsVector";""",
            //
            """ALTER TABLE "Blogs" ADD "TsVector" tsvector GENERATED ALWAYS AS (to_tsvector('english', "Title" || ' ' || coalesce("Description", ''))) STORED;""");
    }

    #endregion PostgreSQL full-text search

    public override async Task Add_required_primitive_collection_to_existing_table()
    {
        await base.Add_required_primitive_collection_to_existing_table();

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" integer[] NOT NULL;""");
    }

    public override async Task Add_required_primitive_collection_with_custom_default_value_to_existing_table()
    {
        await base.Add_required_primitive_collection_with_custom_default_value_to_existing_table();

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" integer[] NOT NULL DEFAULT ARRAY[1,2,3]::integer[];""");
    }

    public override async Task Add_required_primitive_collection_with_custom_default_value_sql_to_existing_table()
    {
        await base.Add_required_primitive_collection_with_custom_default_value_sql_to_existing_table_core("ARRAY[1,2,3]");

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" integer[] NOT NULL DEFAULT (ARRAY[1,2,3]);""");
    }

    [ConditionalFact(Skip = "issue #33038")]
    public override async Task Add_required_primitive_collection_with_custom_converter_to_existing_table()
    {
        await base.Add_required_primitive_collection_with_custom_converter_to_existing_table();

        AssertSql(
            """
ALTER TABLE [Customers] ADD [Numbers] nvarchar(max) NOT NULL DEFAULT N'nothing';
""");
    }

    public override async Task Add_required_primitive_collection_with_custom_converter_and_custom_default_value_to_existing_table()
    {
        await base.Add_required_primitive_collection_with_custom_converter_and_custom_default_value_to_existing_table();

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" text NOT NULL DEFAULT 'some numbers';""");
    }

    public override async Task Add_optional_primitive_collection_to_existing_table()
    {
        await base.Add_optional_primitive_collection_to_existing_table();

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" integer[];""");
    }

    public override async Task Create_table_with_required_primitive_collection()
    {
        await base.Create_table_with_required_primitive_collection();

        AssertSql(
            """
CREATE TABLE "Customers" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text,
    "Numbers" integer[] NOT NULL,
    CONSTRAINT "PK_Customers" PRIMARY KEY ("Id")
);
""");
    }

    public override async Task Create_table_with_optional_primitive_collection()
    {
        await base.Create_table_with_optional_primitive_collection();

        AssertSql(
            """
CREATE TABLE "Customers" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text,
    "Numbers" integer[],
    CONSTRAINT "PK_Customers" PRIMARY KEY ("Id")
);
""");
    }

    public override async Task Create_table_with_complex_type_with_required_properties_on_derived_entity_in_TPH()
    {
        await base.Create_table_with_complex_type_with_required_properties_on_derived_entity_in_TPH();

        AssertSql(
            """
CREATE TABLE "Contacts" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Discriminator" character varying(8) NOT NULL,
    "Name" text,
    "Number" integer,
    "MyComplex_Prop" text,
    "MyComplex_MyNestedComplex_Bar" timestamp with time zone,
    "MyComplex_MyNestedComplex_Foo" integer,
    CONSTRAINT "PK_Contacts" PRIMARY KEY ("Id")
);
""");
    }

    public override async Task Add_required_primitve_collection_to_existing_table()
    {
        await base.Add_required_primitve_collection_to_existing_table();

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" integer[] NOT NULL;""");
    }

    public override async Task Add_required_primitve_collection_with_custom_default_value_to_existing_table()
    {
        await base.Add_required_primitve_collection_with_custom_default_value_to_existing_table();

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" integer[] NOT NULL DEFAULT ARRAY[1,2,3]::integer[];""");
    }

    [ConditionalFact(Skip = "issue #33038")]
    public override async Task Add_required_primitve_collection_with_custom_converter_to_existing_table()
    {
        await base.Add_required_primitve_collection_with_custom_converter_to_existing_table();

        AssertSql("ALTER TABLE [Customers] ADD [Numbers] nvarchar(max) NOT NULL DEFAULT N'nothing';");
    }

    public override async Task Add_required_primitve_collection_with_custom_converter_and_custom_default_value_to_existing_table()
    {
        await base.Add_required_primitve_collection_with_custom_converter_and_custom_default_value_to_existing_table();

        AssertSql("""ALTER TABLE "Customers" ADD "Numbers" text NOT NULL DEFAULT 'some numbers';""");
    }

    protected override string NonDefaultCollation
        => "POSIX";

    public class MigrationsNpgsqlFixture : MigrationsFixtureBase
    {
        protected override string StoreName
            => nameof(MigrationsNpgsqlTest);

        protected override ITestStoreFactory TestStoreFactory
            => NpgsqlTestStoreFactory.Instance;

        public override RelationalTestHelpers TestHelpers
            => NpgsqlTestHelpers.Instance;

        protected override IServiceCollection AddServices(IServiceCollection serviceCollection)
            => base.AddServices(serviceCollection)
                .AddScoped<IDatabaseModelFactory, NpgsqlDatabaseModelFactory>();

        public override DbContextOptionsBuilder AddOptions(DbContextOptionsBuilder builder)
        {
            new NpgsqlDbContextOptionsBuilder(
                    base.AddOptions(builder)
                        // Some tests create expression indexes, but these cannot be reverse-engineered.
                        .ConfigureWarnings(
                            w => { w.Ignore(NpgsqlEfEventId.ExpressionIndexSkippedWarning); }))
                // Various migration operations PG-version sensitive, configure the context with the actual version
                // we're connecting to.
                .SetPostgresVersion(TestEnvironment.PostgresVersion);

            return builder;
        }
    }

    protected override ICollection<BuildReference> GetAdditionalReferences()
        => AdditionalReferences;

    private static readonly BuildReference[] AdditionalReferences = [BuildReference.ByName("Npgsql")];
}