efcore.pg: Npgsql.PostgresException: 42601: syntax error at or near "["
Ran into this error message while trying to perform dotnet ef database update with my initial migration.
All of my explicit updates from the migration went fine according to the verbose output, but I hit a snag with this update to the database.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;
Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "["
at Npgsql.NpgsqlConnector.<DoReadMessage>d__157.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
at Npgsql.NpgsqlConnector.<ReadMessage>d__156.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
dotnet --version 2.1.200
https://github.com/dotnet/core/blob/master/release-notes/download-archives/2.1.200-sdk-download.md
Please let me know what other information I can give you to help out.
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Comments: 35 (14 by maintainers)
@cleversoftware, that repo has existing migrations with indices that have filters such as
[NormalizedName] IS NOT NULL. The migrations were generated on SqlServer (hence the square brackets) and you’re trying to run them on PostgreSQL…If you just want to run this on Npgsql/PostgreSQL, you can simply manually change the SQL in the migrations. If you need the same app to run on both SqlServer and PostgreSQL, please read the EF Core docs on migrations and multiple providers to understand how this works.
Am going to close this as I think everyone’s been encountering the same issue. When trying to use a code sample or an existing project, always check existing migrations and code to see if there’s something coming from another database.
Migrations are C# code which gets generated when you execute
dotnet ef migrations add, and are typically tracked by source control. Now, the C# code which gets generated often contains code that is database-dependent. When defining a filtered index, its WHERE clause is specified in raw SQL, which means that it looks differently across databases: SQL Server quotes identifiers with square brackets, PostgreSQL with double quotes. When you generate the migration code, the raw SQL specified for that index is going to get embedded in that migration, making it database-dependent.In this issue, you guys are taking migrations which were generated on SqlServer, and which contain SqlServer-specific raw SQL (square brackets), and trying to run them on PostgreSQL. This probably means that the application was written for SQL Server, and is not runnable as-is on PostgreSQL.
If all you want to do is run it on PostgreSQL, do the following:
dotnet ef migrations addfrom scratch, making sure you’re targeting PostgreSQL.At this point you should have a freshly-generated migration that will contain double-quotes and be runnable on PostgreSQL.
If you actually want the application to be runnable on both SQL Server and PostgreSQL, thoroughly read the docs I pointed earlier.
Hope this makes things clearer…
Quick fix, is to remove the Migration folder and run “Add-Migration Init” in the package manager console before running “Update-Database”, to flush the SQL Server predefined context.
Thumbs up
I ran into this exception and here’s something to consider as well. I had recently upgraded my asp.net core app from using
Npgsql.EntityFrameworkCore.PostgreSQLversion2.2.4to3.0.1.When I generated new migrations I observed the migrations attempt to change identity columns from using previous
NpgsqlValueGenerationStrategy.SerialColumnto the newNpgsqlValueGenerationStrategy.IdentityByDefaultColumn.Running these new migrations against pre-10.0 PostgreSQL versions generate this error.
Once I changed all occurrences of
NpgsqlValueGenerationStrategy.IdentityByDefaultColumnin the migrations back toNpgsqlValueGenerationStrategy.SerialColumnthe error vanished!@vasicvuk after looking at this again, this may be a result of #286 - index filters weren’t getting applied at all. So in that sense you were right in saying that the driver was ignoring that part of the migration SQL… This was fixed in 2.0.2, which is probably why the SQL Server-specific SQL starts creating issues for you.
Of course, the previous behavior was a bug. You will have to edit your migrations by hand to introduce PostgreSQL equivalents of the SQL Server migrations you currently have.
@jomeno this is expected and documented in the release notes. However, it’s not a good idea to remove the migrations, since your model snapshot will be out of sync.
It’s better to opt out from identity columns as described in the docs, and then the migrations won’t be generated in the first place.
@vasicvuk, neither Npgsql (the driver) nor PostgreSQL magically ignore parts of your SQL. If you try to apply a migration that contains custom SQL with brackets, it will error. If you don’t get an error, then that migration wasn’t applied.
I’ll be happy to explore further if you provide some sort of repro instructions - a sample project with migrations which, when applied to PostgreSQL, somehow works even though it contains square brackets. But right now you’re not giving us any info to go on.
@sguryev I am 100% sure that migration was executed because i will not have 10 tables in Database if this was not executed. I understand that Squere brackets are not valid in Postgres but i think that driver was ignoring that part when executing migration in older version. Anyway i can ensure that again by running old version and then i can write results here.
In product i am developing we always use sql server migration as base since we support multiple database types. But all environments are on Postgres.
@roji I don’t know if it is possible or not but i have this in my migration for 1 year now and we have like 10 environments that are currently running on Postgres that actually used this Migrations. Today i have updated to Entity Framework Core 2.1.3 and .Net Core 2.1 and also to latest version of Npgsql.EntityFrameworkCore.PostgreSQL and since today migrations are not working.
"filter: “[NormalizedName] IS NOT NULL” is present in migration in Initial commit that was a year ago.
Dependencies used
Npgsql.EntityFrameworkCore.PostgreSQL => 2.0.1 IdentityServer4.EntityFramework => 2.1.1 IdentityServer4 => 2.1.3 IdentityServer4.AspNetIdentity => 2.1.0
Maybe it was just ignored in old version of the lib ?
@vasicvuk it’s not really possible for the above to have worked with PostgreSQL, which does not support square brackets to quote identifiers… Please double check what exactly was working before…
IS NOT NULLis required only by SQL Server and it’s a well known issue. PostgreSQL follows the SQL standard and admits multiple null values because any comparison withNULLreturns false (i.e.NULL != NULL). From the documentation: