Postgres migration issue #11698

5 Answer(s)

• 

  0

  ismcagdas created one year ago

  Support Team

  Hi,

  Did you follow this tutorial https://aspnetboilerplate.com/Pages/Documents/EF-Core-PostgreSql-Integration ?

  Save Cancel
• 

  0

  Omar.Altawyan created 9 months ago

  What is your product version? 13

  What is your product type (Angular or MVC)? MVC

  What is product framework type (.net framework or .net core)? .net core

  .EntityFrameworkCore dependencies

  Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.0" Abp.EntityFrameworkCore.EFPlus" Version="9.0.0" Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.0" Microsoft.EntityFrameworkCore.Tools" Version="8.0.0" OpenIddict.EntityFrameworkCore" Version="4.10.0"

  #################################################

  Dear Ismcagdas,

  I follow this tutorial and I facing same Issue.

  steps I already did it:

  • Delete old migrations
  • Inite a new migration

  the generating code for migration is not valid to PostgreSQL

  migrationBuilder.CreateIndex( name: "IX_AppSubscriptionPaymentsExtensionData_SubscriptionPaymentId_~", table: "AppSubscriptionPaymentsExtensionData", columns: new[] { "SubscriptionPaymentId", "Key", "IsDeleted" }, unique: true, filter: "[IsDeleted] = 0");

  Exception:

  Exception data: Severity: ERROR SqlState: 42601 MessageText: syntax error at or near "[" Position: 181 File: scan.l Line: 1245 Routine: scanner_yyerror 42601: syntax error at or near "["

  When I Remove Square bracket it's show me another exception

  Exception data: Severity: ERROR SqlState: 42703 MessageText: column "isdeleted" does not exist Hint: Perhaps you meant to reference the column "AppSubscriptionPaymentsExtensionData.IsDeleted". Position: 181 File: parse_relation.c Line: 3729 Routine: errorMissingColumn

  When I take has hint to add AppSubscriptionPaymentsExtensionData.IsDeleted it's show me another exception missing FROM-clause entry for table "appsubscriptionpaymentsextensiondata"

  Exception data: Severity: ERROR SqlState: 42P01 MessageText: missing FROM-clause entry for table "appsubscriptionpaymentsextensiondata" Position: 181 File: parse_relation.c Line: 3652 Routine: errorMissingRTE

  Save Cancel
• 

  0

  nadav created 9 months ago

  You need to use PostgreSQL dialect inside your DBContext.cs => onModelCreating(ModelBuilder modelBuilder) { ... modelBuilder.Entity<SubscriptionPaymentExtensionData>(b => { b.HasQueryFilter(m => !m.IsDeleted) .HasIndex(e => new { e.SubscriptionPaymentId, e.Key, e.IsDeleted }) .IsUnique() ** .HasFilter(""IsDeleted" = FALSE"); // <=====** }); ... }

  Save Cancel
• 

  0

  alliance225 created 3 months ago

  Hello,

  I have the same issue. I have version 12.3.0 moving from SQL to Postgre. I added this. But still get the error:

  And yes followed: https://aspnetboilerplate.com/Pages/Documents/EF-Core-PostgreSql-Integration?searchKey=postgre

  protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder);

  modelBuilder.Entity<ApplicationLanguageText>()
  .Property(p => p.Value)
  .HasMaxLength(100); // any integer that is smaller than 10485760
  
  
  modelBuilder.Entity<SubscriptionPaymentExtensionData>(b =>
  {
      b.HasQueryFilter(m => !m.IsDeleted)
      .HasIndex(e => new { e.SubscriptionPaymentId, e.Key, e.IsDeleted })
      .IsUnique()
     .HasFilter("\"IsDeleted\" = false");
  });
  

  Applying migration '20240807165429_Initial_Migration'. Failed executing DbCommand (305ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE UNIQUE INDEX "IX_AppSubscriptionPaymentsExtensionData_SubscriptionPaymentId_~" ON "AppSubscriptionPaymentsExtensionData" ("SubscriptionPaymentId", "Key", "IsDeleted") WHERE [IsDeleted] = 0; Npgsql.PostgresException (0x80004005): 42601: erreur de syntaxe sur ou près de « [ »

  POSITION: 181 at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|234_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken) at Npgsql.NpgsqlDataReader.NextResult() at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) at Npgsql.NpgsqlCommand.ExecuteReader(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, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, 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: ERREUR SqlState: 42601 MessageText: erreur de syntaxe sur ou près de « [ » Position: 181 File: scan.l Line: 1245 Routine: scanner_yyerror 42601: erreur de syntaxe sur ou près de « [ »

  POSITION: 181

  Save Cancel
• 

  0

  alliance225 created 3 months ago

  Resolved it by changing the migration class:

  migrationBuilder.CreateIndex( name: "IX_AppSubscriptionPaymentsExtensionData_SubscriptionPaymentId_Key_IsDeleted", table: "AppSubscriptionPaymentsExtensionData", columns: new[] { "SubscriptionPaymentId", "Key", "IsDeleted" }, unique: true, filter: ""IsDeleted" = false");

  Save Cancel