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**
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.
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.
User contributions licensed under CC BY-SA 3.0