Npgsql.PostgresException: 42601: syntax error at or near "["

[bdparrish]

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.

[YohDeadfall]

I'm not familiar with Entity Framework and I don't know who generates migration scripts, but square brackets for quoting identifiers is a feature of SQL Server. Instead, double quotation marks must be used with PostgreSQL.

[bdparrish]

@YohDeadfall - I understand that part about it, but this is not script that I am creating or even code that I am creating. This is all created under the hood by Npsql/EntityFramework. My quick guess is that I am extending my DbContext from IdentityDbContext<IdentityUser> which wants to create all of the tables for roles, users, claims, etc. If I change this to just extend from DbContext, then everything works as advertised.

Below is the script that EF is trying to use created from dotnet ef migrations script -- please be aware that I have removed my custom part of the script for brevity.

You can see there are two specific calls that are being made where [NormalizedName] and [NormalizedUserName] are being used.

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" varchar(150) NOT NULL,
    "ProductVersion" varchar(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

CREATE TABLE "AspNetRoles" (
    "Id" text NOT NULL,
    "ConcurrencyStamp" text NULL,
    "Name" varchar(256) NULL,
    "NormalizedName" varchar(256) NULL,
    CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetUsers" (
    "Id" text NOT NULL,
    "AccessFailedCount" int4 NOT NULL,
    "ConcurrencyStamp" text NULL,
    "Email" varchar(256) NULL,
    "EmailConfirmed" bool NOT NULL,
    "LockoutEnabled" bool NOT NULL,
    "LockoutEnd" timestamptz NULL,
    "NormalizedEmail" varchar(256) NULL,
    "NormalizedUserName" varchar(256) NULL,
    "PasswordHash" text NULL,
    "PhoneNumber" text NULL,
    "PhoneNumberConfirmed" bool NOT NULL,
    "SecurityStamp" text NULL,
    "TwoFactorEnabled" bool NOT NULL,
    "UserName" varchar(256) NULL,
    CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetRoleClaims" (
    "Id" int4 NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    "RoleId" text NOT NULL,
    CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserClaims" (
    "Id" int4 NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    "UserId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserLogins" (
    "LoginProvider" text NOT NULL,
    "ProviderKey" text NOT NULL,
    "ProviderDisplayName" text NULL,
    "UserId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
    CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserRoles" (
    "UserId" text NOT NULL,
    "RoleId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
    CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserTokens" (
    "UserId" text NOT NULL,
    "LoginProvider" text NOT NULL,
    "Name" text NOT NULL,
    "Value" text NULL,
    CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
    CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");

CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;

CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");

CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");

CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");

CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");

CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName") WHERE [NormalizedUserName] IS NOT NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20180514204732_initial', '2.0.3-rtm-10026');

[bdparrish]

Just to make sure I am giving you full insight...

public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();

            var connectionString = Configuration.GetConnectionString("DbContext");
            services.AddEntityFrameworkNpgsql().AddDbContext<DbContext>(options => options.UseNpgsql(connectionString));
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc();
        }
    }

public class DbContext : IdentityDbContext<IdentityUser>
    {
        public static readonly LoggerFactory logger
            = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

        private IHostingEnvironment _env;

        public DbContext(IHostingEnvironment env)
        {
            _env = env;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var builder = new ConfigurationBuilder()
                   .AddJsonFile("appsettings.json");

            if (_env.IsDevelopment())
            {
                builder = new ConfigurationBuilder()
                       .AddJsonFile("appsettings.Development.json");
            }

            IConfigurationRoot configuration = builder.Build();

            optionsBuilder
                .UseLoggerFactory(logger)
                .UseNpgsql(configuration["ConnectionStrings:DbContext"]);
        }

        // REMOVED FOR BREVITY

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

            // REMOVED FOR BREVITY
        }
    }

[roji]

@bdparrish which version of EF Core are you using exactly? I just created a totally vanilla ASP.NET Core MVC app (dotnet new mvc -au Individual) with EF Core 2.1.0-rc1, set up Npgsql.EntityFrameworkCore.PostgreSQL as the provider, and my generated script contains the following line:

CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName");

Now that there is no WHERE clause on the index like in your example - are you sure you nobody's customizing the model in your application to add that?

[bdparrish]

@roji, I am using <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.3" />

Added with dotnet add package Microsoft.EntityFrameworkCore.Tools

[roji]

Any change you could give 2.1.0-rc1 a run to see if the results are different?

[karthicksundararajan]

I got same error in 2.1.0-rc1

"42601: syntax error at or near \"[\""

{CREATE UNIQUE INDEX "RoleNameIndex" ON "Role" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL}

" at Npgsql.NpgsqlConnector.<DoReadMessage>d__157.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\n at System.Runtime.CompilerServices.ValueTaskAwaiter1.GetResult()\n at Npgsql.NpgsqlConnector.<ReadMessage>d__156.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n at Npgsql.NpgsqlConnector.<ReadMessage>d__156.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\n at System.Runtime.CompilerServices.ValueTaskAwaiter1.GetResult()\n at Npgsql.NpgsqlConnector.<ReadExpecting>d__1631.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\n at System.Runtime.CompilerServices.ValueTaskAwaiter1.GetResult()\n at Npgsql.NpgsqlDataReader.d32.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n at Npgsql.NpgsqlDataReader.NextResult()\n at Npgsql.NpgsqlCommand.d71.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n at System.Runtime.CompilerServices.TaskAwaiter1.GetResult()\n at System.Runtime.CompilerServices.ValueTaskAwaiter1.GetResult()\n at Npgsql.NpgsqlCommand.d__84.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\n at Npgsql.NpgsqlCommand.ExecuteNonQuery()\n at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues)\n at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)\n at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)\n at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection)\n at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)\n at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)\n at Deviser.Core.Data.Repositories.InstallationProvider.InstallPlatform(InstallModel installModel) in /home/karthick/Projects/Deviser/deviserplatform/src/Deviser.Core/Deviser.Core.Data/Repositories/InstallationProvider.cs:line 110\n at Deviser.WI.Controllers.InstallController.Index(InstallModel installModel) in /home/karthick/Projects/Deviser/deviserplatform/src/Deviser.WI/Controllers/InstallController.cs:line 70"`

[roji]

I'm sorry, but I couldn't reproduce this with a simple ASP.NET app. Can someone please post a full and preferably minimal code sample or project which triggers the issue please?

[cleversoftware]

Hi. You can get this f.e. https://github.com/emonney/QuickApp (DesignTimeDbContextFactory), change provider to Npgsql and try to database-update. Tnx!

[roji]

@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.

[cleversoftware]

Thumbs up

[bdparrish]

@roji Can you explain a little clearer as to what your understanding is? This problem still seems to be an issue. The provided sample app from @cleversoftware has a single migration script for the initial migration. My project also was an initial migration using Npsql from the start. So my erroneous [ are from scripts created by EF in the background when building the migration scripts from all that I can see and understand.

[roji]

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:

• Find the place in the application's context where the raw SQL is specified, and convert it to PostgreSQL (i.e. replace square brackets with double quotes).
• Delete the existing migration C# code, which already contains the square brackets
• Regenerate a migration on PostgreSQL, i.e. run dotnet ef migrations add from 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...

[karthicksundararajan]

@roji

Thanks for the guidance, the clean migration on PostgreSQL works for me.

This is bit of strange that SqlServer-specific raw SQL are generated when targeting SQLServer. May be there should be a common understanding among entity framework providers to generate clean migration scripts as Npgsql.EntityFrameworkCore.PostgreSQL.

[roji]

This is bit of strange that SqlServer-specific raw SQL are generated when targeting SQLServer. May be there should be a common understanding among entity framework providers to generate clean migration scripts as Npgsql.EntityFrameworkCore.PostgreSQL.

Filtered indices are a feature which require the user to specify the filter in raw SQL, which is why it's necessarily database-specific. Maybe in the future they EF Core will allow you to specify the index's filter clause via an expression, and translate that to raw SQL only when actually applying the migration to the database (and the SQL will thus be generated specifically for the database type being migrated). But this isn't the case right now.

[Theoistic]

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.

[sguryev]

Hi Guys. Sorry for the coming such old item back to life (and sorry if it's a wrong one) but it's Friday 13th and zombie should be at least somewhere!

I have fixed the [ issue myself. Actually I have decided to use the same approach as @SperoSophia has suggested. But I have compared the snapshots (same model but two diff providers) and here is what my concern about: As you see - there is no FILTER call generated for the NPG. I'm new to the Postgre but I guess that constraint will be fired on the NPG during the second NULL insert. Am I right?

Model contains simple 1-0..1 relation:

[YohDeadfall]

IS NOT NULL is 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 with NULL returns false (i.e. NULL != NULL). From the documentation:

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison.

[felixlindemann]

..., 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... [...]

@roji Thanks for that! Solved my Problem.

[vasicvuk]

The interested thing is that: filter: "[NormalizedUserName] IS NOT NULL"

worked with Entity Framework Core 2.0 with Npgsql.EntityFrameworkCore.PostgreSQL 2.0. This is crashing backwards compatibility.

[roji]

@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...

[vasicvuk]

@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 ?

[sguryev]

@vasicvuk please make sure that migration was applied. @roji 100% right. You can open NPGAdmin and try to execute any statement with the square brackets.

[vasicvuk]

@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]

@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.

[roji]

@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.

[vasicvuk]

@roji Thanks for explanation. It seems that Bug was a feature for some of us :).

[roji]

@vasicvuk thanks for understanding, and sorry it took some time to remember this bug...

If you're doing migrations on multiple database systems, in many cases you have no choice but to edit your migrations and add conditions - you can check which provider is being used to apply the migration, and choose the correct SQL accordingly.

[sopheakmorm]

@roji , I met the same problem and try with solution that you suggested and it working fine. Find the place in the application's context where the raw SQL is specified, and convert it to PostgreSQL (i.e. replace square brackets with double quotes).

[roji]

It seems a lot of people are hitting this by starting out from the ASP.NET Identity templates (which are meant for SQL Server) and converting to PostgreSQL. I'll try to see about PostgreSQL-specific templates getting integrated there so this can all be avoided.

[Etnic]

Remove your migration folder and create new migration and udpate database.

[jomeno]

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.PostgreSQL version 2.2.4 to 3.0.1.

When I generated new migrations I observed the migrations attempt to change identity columns from using previous NpgsqlValueGenerationStrategy.SerialColumn to the new NpgsqlValueGenerationStrategy.IdentityByDefaultColumn.

Running these new migrations against pre-10.0 PostgreSQL versions generate this error.

Once I changed all occurrences of NpgsqlValueGenerationStrategy.IdentityByDefaultColumn in the migrations back to NpgsqlValueGenerationStrategy.SerialColumn the error vanished!

[roji]

@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.

[jomeno]

@roji Noted. Indeed this is a much better approach, thanks.

[ghost]

just change the lines with: filter: "[NormalizedUserName] IS NOT NULL"); to filter: "\"NormalizedUserName\" IS NOT NULL"); in 00000000000000_CreateIdentitySchema.cs In other words. Change the brackets to be escaped double quotes (backslash + doublequote for each bracket)