Base solution for your next web application
Open Closed

Postgres migration issue #11698


User avatar
0
osweater created

Hi, I am trying to use Postgres on the latest zero version but when updating the database I get this error

`Failed executing DbCommand (225ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE UNIQUE INDEX "IX_AppSubscriptionPaymentsExtensionData_SubscriptionPaymentId_Key_IsDeleted" ON "AppSubscriptionPaymentsExtensionData" ("SubscriptionPaymentId", "Key", "IsDeleted") WHERE [IsDeleted] = 0; Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "["

POSITION: 193 at Npgsql.Internal.NpgsqlConnector.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: ERROR SqlState: 42601 MessageText: syntax error at or near "[" Position: 193 File: scan.l Line: 1188 Routine: scanner_yyerror 42601: syntax error at or near "["`

I think its in this block of code migrationBuilder.CreateIndex( name: "IX_AppSubscriptionPaymentsExtensionData_SubscriptionPaymentId_~", table: "AppSubscriptionPaymentsExtensionData", columns: new[] { "SubscriptionPaymentId", "Key", "IsDeleted" }, unique: true, filter: "[IsDeleted] = 0");

Thanks in advance,


5 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team
  • User Avatar
    0
    Omar.Altawyan created

    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

  • User Avatar
    0
    nadav created

    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"); // <=====** }); ... }

  • User Avatar
    0
    alliance225 created

    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&lt;ApplicationLanguageText&gt;()
    .Property(p => p.Value)
    .HasMaxLength(100); // any integer that is smaller than 10485760
    
    
    modelBuilder.Entity&lt;SubscriptionPaymentExtensionData&gt;(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

  • User Avatar
    0
    alliance225 created

    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");