Error syntax error at or near case

PostgreSQL — Syntax error at or near «.» #4216

I just transitioned a sequelize project off of sqlite and onto PostgreSql. I’m finding Postgres isn’t liking the «SELECT col AS» naming conventions that Sequelize is generating. All of the queries with relationships are being aliased as names with dots in them, which is generating syntax errors. For instance, a basic auth check query for checking whether the active user (account.id = 3) has a task with the code «viewProfile», is generating the following query:

Which, is generating the following postgres error:

All of this worked fine with the sqlite dialect. Any help would be greatly appreciated.

The text was updated successfully, but these errors were encountered:

Here’s a better example, I know I haven’t included my model definitions — but this is an eager selection of a simple relationship between a «role» table and a «task» table, with a «role_tasks» association table.

Generated PostgreSQL query (missing quotes on inner identifiers):

Correct PostgreSQL query would be (with quotes around tasks.roleTasks):

Please do post model definitions and associations so we can actually reproduce the problem.

You have have quoteIdentifiers set to false by any chance?

I cleaned up my models significantly, removing all ‘field’, ‘tableName’ and ‘as’ properties (when defining relationships). this still didn’t fix the issue. I then realized I had quoteIdentifiers set to false, and I changed it to true — and magically it started working!

I’m going to work on reverting my models back to their original definition, to see if the problem was strictly related to quoteIdentifiers, or a combination of issues.

So it kind of feels like quoteIdentifiers is a mandatory option, no? I intentionally set it to false, because I didn’t really want to bother quoting all of my mixed-case tables when querying from the command line. but I can obviously live with that.

Support for quoteIdentifiers: false is spotty at best, some 3rd party contributions have made efforts at improving it but it’s not great.

If you create your table names with lowercase you should still be able to query them fine from the command line (atleast it works for me).

Closing as «please don’t use quoteIdentifiers: false unless you really, really, have to!»

© 2022 GitHub, Inc.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

Получение ошибки при попытке использовать `CASE` в PostgreSQL

Добрый день, я пытаюсь выполнить запрос в PgAdmin, чтобы увеличить значение в моей таблице, если оно существует, если нет, оно создаст строку, однако я получаю сообщение об ошибке

Я также пытался использовать upsert

Вместо этого выдает другую ошибку

В этом контексте, я думаю, вы захотите использовать if , а не case . Я считаю, что case используется только как выражение в другом утверждении, например, в предложении select или where . См. здесь для примера оператора if.

Тоже пробовал, смотрите мое редактирование @David784

SQL не имеет оператора IF. Вы можете использовать это только в PL/pgSQL, который также поддерживает CASE утверждение (в SQL есть только CASE выражения)

Хорошо, подскажите, как исправить ошибку? Используя стандартный SQL?

Ни один из этих запросов не является допустимым SQL. Это похоже на некоторый код из функции/процедуры pl/pgsql, но неполный и, следовательно, недействительный.

Juse использует insert . on conflict , чтобы сделать то, что известно как «UPSERT».

Пожалуйста, смотрите мое редактирование.

Что-то вроде этого может работать, но это немного зависит от структуры таблицы:

Это работает, единственное, что не добавляется, когда user_id не существует, он оставляет его равным 0. Это должно делать — если user_id не существует — создать пользователя, а counter должно быть 1, если оно существует , просто добавьте единицу к текущему значению счетчика. Однако большое спасибо за ответ!

Используйте значение по умолчанию 1, если 0 не является значением по умолчанию, которое вы ищете.

Источник

@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');