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,
3 Answer(s)
-
0
Hi,
Did you follow this tutorial https://aspnetboilerplate.com/Pages/Documents/EF-Core-PostgreSql-Integration ?
-
0
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
-
0
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"); // <=====** }); ... }
