Data migration in code first error using postgresql as database in .net core 3.0

Question

I am migrating data using code first approach and db is postgresql, on add-migration is working fine,but update-database is giving error as"42601: syntax error at or near "GENERATED", more details below:

> PM> add-migration migration
>     Build started...
>     Build succeeded.
>     To undo this action, use Remove-Migration.
>     PM> update-database
>     Build started...
>     Build succeeded.
>     [15:18:48 Error] Microsoft.EntityFrameworkCore.Database.Command
>     Failed executing DbCommand (298ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
>     CREATE TABLE "Customers" (
>         "CustomerId" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
>         "CustomerName" text NULL,
>         CONSTRAINT "PK_Customers" PRIMARY KEY ("CustomerId")
>     );
>     
>     Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "GENERATED"
>        at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
>     --- End of stack trace from previous location where exception was thrown ---
>        at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
>     --- End of stack trace from previous location where exception was thrown ---
>        at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
>        at Npgsql.NpgsqlDataReader.NextResult()
>        at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
>        at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
>        at Npgsql.NpgsqlCommand.ExecuteNonQuery()
>        at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject
> parameterObject)
>        at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection
> connection, IReadOnlyDictionary`2 parameterValues)
>        at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1
> migrationCommands, IRelationalConnection connection)
>        at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String
> targetMigration)
>        at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String
> targetMigration, String contextType)
>        at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String
> targetMigration, String contextType)
>        at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
>        at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action
> action)
>       Exception data:
>         Severity: ERROR
>         SqlState: 42601
>         MessageText: syntax error at or near "GENERATED"
>         Position: 63
>         File: src\backend\parser\scan.l
>         Line: 1067
>         Routine: scanner_yyerror
>     42601: syntax error at or near "GENERATED"

(As this is code first approach so, below are the model) Model:

     public class Customer1
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
    }


**Update: Got a solution:
in migrationbuilder(created after add-migration command),i simply changed** 
NpgsqlValueGenerationStrategy.IdentityByDefaultColumn 
to 
NpgsqlValueGenerationStrategy.serialcolumn
**and saved it ,and then run the command update-database ,and it worked**

What version of postgres are you using? As an aside, I'd strongly recommend avoiding identifier names that require quotes. — Jeremy
– Jeremy, Commented Dec 7, 2019 at 10:49

Ryan · Accepted Answer · 2019-12-09 06:20:11Z

2

You seem to be activating the IDENTITY columns feature, which was only introduced in PostgreSQL 10.(while you are 9.4.20)

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

answered Dec 9, 2019 at 6:20

Ryan

20.3k10 gold badges44 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Belayer · Accepted Answer · 2019-12-08 05:43:47Z

0

Seriously! I doubt it's Postgres 3.x anything (released in '91). That may be your .net version. Please run this query: select version();
Your table definition uses "GENERATED BY DEFAULT AS IDENTITY". This initially appeared in version 10. If your version less then change that definition to:

 create table Customers (
        CustomerId   serial 
        CustomerName text null,
        constraint pk_customers primary key (CustomerId)
    );

Note: I've removed the double quotes (") and would suggest you do the same. They just are not worth the trouble they cause.

answered Dec 8, 2019 at 5:43

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

3 Comments

Brijesh Ray Over a year ago

I am just new to postgresql, got the version now its, postgresql 9.4.20

Brijesh Ray Over a year ago

I cannot create a table direcly in postgres ,as its code -first approach

Belayer Over a year ago

The 10th line in your post has a Create Table. Since you are using 9.4 the "Generated Always" structure does not exist. However, it got there change it to what I posted above. Since this would be the first access you have successfully failed so now you can create it properly.

Collectives™ on Stack Overflow

Data migration in code first error using postgresql as database in .net core 3.0

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related