The migration generated by EF Core for Postgresql appears to give bad syntax, is this some kind of version error?

Question

I am trying to figure out setting up a .net core project in linux, using Postgresql as my database server.

I have started with a default .net core 2.2 web api project which gives you a WeatherForecast entity.

I have appended an ID to this, annotated it with a key and have generated an initial migration;

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "WeatherForecasts",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                Date = table.Column<DateTime>(nullable: false),
                TemperatureC = table.Column<int>(nullable: false),
                Summary = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_WeatherForecasts", x => x.Id);
            });
    }

When I try to apply the migration, I get the following error;

Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "GENERATED"

The error suggests the database has an issue with the word 'GENERATED'

The debugger shows the exact query attempted against the database;

CREATE TABLE "WeatherForecasts" (
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "Date" timestamp without time zone NOT NULL,
    "TemperatureC" integer NOT NULL,
    "Summary" text NULL,
    CONSTRAINT "PK_WeatherForecasts" PRIMARY KEY ("Id")
);

I opened a terminal window and connected to the postgresql cli and tried the create directly, it confirmed that the GENERATED word was causing the error.

Any ideas as to what this GENERATED refers to? Do I need to install a different postgres version? (it's currenty 9.6.15)

My CsProj has the following packages for Entity Framework and Postgresql;

"Microsoft.EntityFrameworkCore.Design" Version="3.0.0"

"Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.0.0"

"Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.1"

Many thanks

Answer 1

If you like to use old PostgreSQL for some reasons (i personaly understand it) you can specify your version in overrided OnConfiguring method. It works for me perfectly:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql("Server=127.0.0.1;Port=5432;Database=fantyPayments;User Id=postgres;Password = postgres;", options =>
            {
                options.SetPostgresVersion(new Version("9.6"));
            });
            base.OnConfiguring(optionsBuilder);
        }

Check more options for Version class. It may help you with many issues.

Error with Syntax at this stage is due to big changes in version 10.* PostgreSQL. Default Npgsql driver for EF Core is set to the newest PostgreSQL version.

As @Xing Zou wrote, IDENTITY columns feature is reason why you can't use syntax of PgSQL 10.* in PgSQL 9.* version.