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

0

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

c#
postgresql
asp.net-core
asked on Stack Overflow Sep 28, 2019 by Aeptitude

2 Answers

1

You seem to be activating the IDENTITY columns feature, which was only introduced in PostgreSQL 10.I suggest that you could upgrade to a newer version of PostgreSQL.

Refer to https://dba.stackexchange.com/questions/198777/how-to-add-a-postgresql-10-identity-column-to-an-existing-table

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

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

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

Besides,the packages you have installed are for asp.net core 3.0 but you seems to have an asp.net core 2.2 web api project which may not support.

answered on Stack Overflow Sep 30, 2019 by Xing Zou
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.

answered on Stack Overflow Mar 23, 2020 by Kamil • edited Mar 23, 2020 by Kamil

User contributions licensed under CC BY-SA 3.0